Crosstable

[Sharbel]

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

[pedrohenriqueperna · Accepted solution]

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.)

[Sharbel]

Thanks!