topic Crosstable in App Development

Crosstable

Sharbel — Sat, 19 Aug 2023 12:57:15 GMT

Hi,

I have the following table (Source):

I am trying to transform it to the following table (Target):

tried to use the Crossable syntax :

Target:

CrossTable (Amnt_Type,Amount)

Load

Emp_No,

Resident Source;

Drop Table Source;

but didn't got the desired Target table

Any suggestions?

Best regards,

Sharbel

Re: Crosstable

pedrohenriqueperna — Sat, 19 Aug 2023 14:26:45 GMT

Hi!

Try this:

Raw:
LOAD * Inline [
Emp_No, Date, Amnt1, Amnt2, Amnt3, Amnt4
25, 2023-06-30, 5396.4, 423.9, 253, 200
25, 2023-07-31, 5146.4, 303.9, 193, 107
33, 2023-06-30, 4896.4, 183.9, 133, 67
33, 2023-07-31, 4646.4, 63.9, 73, 27
];

Cross1:
Crosstable (Amnt_Type, Amount, 2)
LOAD
Emp_No,
Date,
Amnt1,
Amnt2,
Amnt3,
Amnt4
Resident Raw;

Drop Table Raw;

As you have more than 1 qualifier field, you must use the function's third parameter to point out how many qualifiers you're using (in this case 2) and then list every qualifier before the attribute fields (in this case amnt1, amnt2, etc.)

Re: Crosstable

Sharbel — Sat, 19 Aug 2023 19:51:10 GMT

Thanks!