Help with cross table

[josemaria_cabre]

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

[marcus_sommer · Accepted solution]

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]

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]

Your solution worked perfectly, thank you Marcus!