topic Re: Help with cross table in App Development

Help with cross table

josemaria_cabre — Mon, 01 Jul 2024 12:53:16 GMT

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

Re: Help with cross table

marcus_sommer — Mon, 01 Jul 2024 13:10:33 GMT

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;

Re: Help with cross table

josemaria_cabre — Mon, 01 Jul 2024 13:34:36 GMT

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

Re: Help with cross table

marcus_sommer — Mon, 01 Jul 2024 13:44:51 GMT

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.

Re: Help with cross table

josemaria_cabre — Tue, 02 Jul 2024 08:05:03 GMT

Your solution worked perfectly, thank you Marcus!