Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys...
greeting of the day.
I have an excel file as below.
Group A | Group B | Month |
---|---|---|
Company A | Company B | Jan |
Company B | Company F | Feb |
Company C | Company D | Mar |
Company D | Company D | Apr |
Company F | Company D | may |
So hear the logic is in the month of Jan Company A has been taken by Company B,
in the month of Feb Company B has been Taken by Company F,
in the month of Mar Company C has been taken by Company D,
in The month of May company F also taken by Company F
my resultant table in qlik will be like below.
Group A | Jan | Feb | Mar | Apr | May | |
---|---|---|---|---|---|---|
Company A | Company B | Company F | Company F | Company F | Company D | |
Company B | Company B | Company F |
| Company F | Company D | |
Company C | Company C | Company C | Company D | Company D | Company D | |
Company D | Company D | Company D | Company D |
| Company D | |
Company F | Company F | Company F | Company F |
| Company D |
Plz help me how to achieve this in qlikview.
Thanks in advance
May be this
Table:
LOAD GroupA,
GroupB,
Month(Date#(Month, 'MMM')) as Month;
LOAD * INLINE [
GroupA, GroupB, Month
Company A, Company B, Jan
Company B, Company F, Feb
Company C, Company D, Mar
Company D, Company D, Apr
Company F, Company D, may
];
FinalTable:
LOAD Distinct GroupA,
If(Num(Month) = 1, GroupB, GroupA) as 1,
If(Num(Month) = 1, GroupB, GroupA) as Jan,
Month,
GroupB
Resident Table;
FOR i = 1 to FieldValueCount('Month')-1
LET vField = FieldValue('Month', $(i)+1);
LET j = $(i) + 1;
Left Join (FinalTable)
LOAD GroupA as $(i),
If(Num(Month) <= $(j), GroupB, [$(i)]) as $(j),
If(Num(Month) <= $(j), GroupB, [$(i)]) as $(vField)
Resident FinalTable;
NEXT i
DROP Table Table;
You have to create the generic load for this try generic load for this.
May be this
Table:
LOAD GroupA,
GroupB,
Month(Date#(Month, 'MMM')) as Month;
LOAD * INLINE [
GroupA, GroupB, Month
Company A, Company B, Jan
Company B, Company F, Feb
Company C, Company D, Mar
Company D, Company D, Apr
Company F, Company D, may
];
FinalTable:
LOAD Distinct GroupA,
If(Num(Month) = 1, GroupB, GroupA) as 1,
If(Num(Month) = 1, GroupB, GroupA) as Jan,
Month,
GroupB
Resident Table;
FOR i = 1 to FieldValueCount('Month')-1
LET vField = FieldValue('Month', $(i)+1);
LET j = $(i) + 1;
Left Join (FinalTable)
LOAD GroupA as $(i),
If(Num(Month) <= $(j), GroupB, [$(i)]) as $(j),
If(Num(Month) <= $(j), GroupB, [$(i)]) as $(vField)
Resident FinalTable;
NEXT i
DROP Table Table;
superb..... Thank you So much Sunny talwar , you are genius.......
I am glad I was able to help. I would suggest you to close this thread if you got what you were looking to get by marking correct and helpful responses
Qlik Community Tip: Marking Replies as Correct or Helpful
Best,
Sunny