Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
josemaria_cabre
Contributor III
Contributor III

Help with cross table

Hi,

I'm trying to turn this table:

date a total a ok a err a others b total b ok b err
10/01/2024 10 6 4 3 4 4 0

into:

date type total ok err others
10/01/2024 a 10 6 4 3
10/01/2024 b 4 4 0 0

 

I tried to do this with a crosstable prefix in my load script, but it doesn't work. Can anyone please help with this?

Thanks in advance, 

Jose

Labels (2)
1 Solution

Accepted Solutions
marcus_sommer

You could apply a normal crosstable-load and on this result you do the final differentiation, maybe like this:

t1: crosstable(Cat, Value, 1) load * from YourSource;

t2: load date, subfield(Cat, ' ', 1) as type, subfield(Cat, ' ', 2) as Cat, Value resident t1;

drop tables t1;

View solution in original post

4 Replies
marcus_sommer

You could apply a normal crosstable-load and on this result you do the final differentiation, maybe like this:

t1: crosstable(Cat, Value, 1) load * from YourSource;

t2: load date, subfield(Cat, ' ', 1) as type, subfield(Cat, ' ', 2) as Cat, Value resident t1;

drop tables t1;

josemaria_cabre
Contributor III
Contributor III
Author

Hi Marcus,

that worked great!, but,

what if I had another dimension apart from date, e.g (status and planned dimensions):

date status planned a total a ok a err a others b total b ok b err
10/01/2024 OK yes 10 6 4 3 4 4 0


how can I add those 2 dimensions to t2 table?

Thanks in advance,
Jose

marcus_sommer

The third parameter von the crosstable-statement defines how many fields should be loaded before the crosstable-transforming starts, means this one:

crosstable(Cat, Value, 3)

If you had different sources like sales and planned you could concatenate both - after the above shown two load-steps. If these data comes from another source to extend the information you may just join/map it against a sensible KEY.

josemaria_cabre
Contributor III
Contributor III
Author

Your solution worked perfectly, thank you Marcus!