Skip to main content
Solved

Lists aggregate

pratap
Contributor
Forum|alt.badge.img+11

Hi All,

I have an excel file with header-1 names and header-2 names. I have used AttributeSplitter and made list for both like header-1_list and header-2_list for each record. Now I want to aggregate the values like mentioned below...

If 1 record in excel contains

header-1_list contains 0,1,2 values

header-2_list contains A,B,C,D,E values

Output should be 15 records which contains 0-A,0-B,0-C,0-D,0-E,1-A,1-B,1-C,1-D,1-E,2-A,2-B,2-C,2-D,2-E as values.

(I have attached sample excel file and my progress work bench to this message for better understanding)

Thank you in advance

Pratap

 

Best answer by takashi

Hi @pratap, I would use the InlineQuerier to perform CROSS JOIN.

0684Q00000ArKrrQAF.png

SQL statement:

select t1.h||'-'||t2.h as header
from t1 cross join t2

View original
Did this help you find an answer to your question?
This post is closed to further activity.
It may be a question with a best answer, an implemented idea, or just a post needing no comment.
If you have a follow-up or related question, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.

6 replies

takashi
Influencer
  • takashiInfluencer
  • Best Answer
  • October 27, 2016

Hi @pratap, I would use the InlineQuerier to perform CROSS JOIN.

0684Q00000ArKrrQAF.png

SQL statement:

select t1.h||'-'||t2.h as header
from t1 cross join t2

takashi
Influencer
  • takashiInfluencer
  • October 28, 2016
takashi wrote:

Hi @pratap, I would use the InlineQuerier to perform CROSS JOIN.

0684Q00000ArKrrQAF.png

SQL statement:

select t1.h||'-'||t2.h as header
from t1 cross join t2

another approach:

 

0684Q00000ArMU2QAN.png

pratap
Contributor
Forum|alt.badge.img+11
  • pratapContributor
  • Author
  • Contributor
  • October 28, 2016

Hi @takashi,

Thank you for the reply, it suits exactly the requirement.

Is it possible to add the xlsx_row_id or unique number to each record?

There are several records as shown in excel file and I want to know which records (of target) represent for which record (of source i.e. xlsx_row_id)...

Thank you once again,

Pratap

takashi
Influencer
  • takashiInfluencer
  • October 28, 2016
pratap wrote:

Hi @takashi,

Thank you for the reply, it suits exactly the requirement.

Is it possible to add the xlsx_row_id or unique number to each record?

There are several records as shown in excel file and I want to know which records (of target) represent for which record (of source i.e. xlsx_row_id)...

Thank you once again,

Pratap

In the second approach, the original "xlsx_row_id" attribute will be propagated to the resulting features naturally. Just expose it in the reader feature type.

 

pratap
Contributor
Forum|alt.badge.img+11
  • pratapContributor
  • Author
  • Contributor
  • October 28, 2016

Thanks @takashi, I got it with another approach with feature Merger.

gio
Contributor
Forum|alt.badge.img+15
  • gioContributor
  • Contributor
  • October 28, 2016

cross join is just a unconditional featuremerge...also known as a certesian product

Helpful Members This Week

Community Stats

31,250
Posts
118,509
Replies
38,840
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.