Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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