Making a dimension from different columns in csv files.

[Anonymous]

Hi.

I'm new to this and would appreciate some guidance.

I would like to create a pivot table where I can group (headlines for columns in a .csv file)  from different files (.csv files) to one dimension.

Lets say I have 2 documents. A and B and 3 dimension in each one.

In document A I have

dimension A.a with id. 1, 2, 3

dimension A.b with id 4, 5 ,6

dimension A.c with id 7,8

In document B I have

dimension B.a with id. 3, 7, 5

dimension B.b with id1, 2 ,4

dimension B.c with id 6, 8

The numbers would be added up but only be able to be present in one.

All A.a to be added up as one,

then B.a (those who are in B.a but not in A.a)

then A.b (those who are in A.b but not in A.a or B.a. and so on.

So it would be able to create a pivottabe that in the end would like something like this

dimension      count id

A.a                      3

B.a                      2

A.b                      2
B.b                      0

A.c                      1
B.c                      0

Could this be done and where to start to do this?

[marcus_sommer]

I could imagine the following approach:

- loading both csv-files and concatenate them

- maybe adding a recno() and similar stuff to be able to sort the records appropriate

- loading the above within a resident-load and applying the sorting

- within this load cummulating the id's like:

  if(rowno() = 1, id, peek('id_cum') & '+' & id) as id_cum

- then generating a counter-flag of the match from id within id_cum, maybe like:

  if(substringcount(if(rowno() = 1, id, peek('id_cum') & '+' & id), id), 0, 1) as Flag

- then using sum(Flag) as expression within your pivot

- Marcus