Skip to main content
Solved

Lists aggregate

  • October 27, 2016
  • 6 replies
  • 108 views
pratap
Contributor
Forum|alt.badge.img+12

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

This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, 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
Celebrity
  • takashiCelebrity
  • 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

Why not inspect features with Visual/Data Preview and Feature/Record Information before writing them into a destination dataset?
takashi
Celebrity
  • takashiCelebrity
  • October 28, 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

another approach:

 

0684Q00000ArMU2QAN.png

Why not inspect features with Visual/Data Preview and Feature/Record Information before writing them into a destination dataset?
pratap
Contributor
Forum|alt.badge.img+12
  • 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
Celebrity
  • takashiCelebrity
  • 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

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.

 

Why not inspect features with Visual/Data Preview and Feature/Record Information before writing them into a destination dataset?
pratap
Contributor
Forum|alt.badge.img+12
  • 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

Community Stats

32,154
Posts
122,047
Replies
39,812
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement
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.