Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
someone who can help me achieving this result?
many Thanks for looking into it
Result:
Month;amount (launch);amount(target)
Jan;120;50
feb;o;200
i would like to have the breakdown at lower levels (project) too, but ignored it to simplify
Original data:
Project;month(launch);amount(launch);month(target);amount(target)
1;jan;100;feb;200
2;jan;20;jan;50
What you need here is to use a Canonical Date here
Simplified version of the script for the simplified data you provided
Table: LOAD Project, Month(Date#(Launch_Month, 'MMM')) as Launch_Month, Launch_Amount, Month(Date#(Target_Month, 'MMM')) as Target_Month, Target_Amount; LOAD * INLINE [ Project, Launch_Month, Launch_Amount, Target_Month, Target_Amount 1, Jan, 100, Feb, 200 2, Jan, 20, Jan, 50 ]; DateBridge: LOAD Project, Launch_Month as Month, 'Launch' as DateType Resident Table; LOAD Project, Target_Month as Month, 'Target' as DateType Resident Table;
What you need here is to use a Canonical Date here
Simplified version of the script for the simplified data you provided
Table: LOAD Project, Month(Date#(Launch_Month, 'MMM')) as Launch_Month, Launch_Amount, Month(Date#(Target_Month, 'MMM')) as Target_Month, Target_Amount; LOAD * INLINE [ Project, Launch_Month, Launch_Amount, Target_Month, Target_Amount 1, Jan, 100, Feb, 200 2, Jan, 20, Jan, 50 ]; DateBridge: LOAD Project, Launch_Month as Month, 'Launch' as DateType Resident Table; LOAD Project, Target_Month as Month, 'Target' as DateType Resident Table;