Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone,
I have a challange for which I would require your help.
We have some cost centers and because of some organisational changes their names were changed and depending on the time I call the Report, I want to see the correct name for the respective CC. Our date system is YYYYMM.
CC | Name | ValidFrom | ValidTo |
---|---|---|---|
100 | Business Development | NULL | NULL |
200 | Indirect Costs | NULL | NULL |
200 | Direct Costs | 201701 | NULL |
300 | Other | NULL | NULL |
300 | Purchasing | 201801 | NULL |
If there is only 1 record for a CC, ex: 100, then ValidFrom should be 201401 and ValidTo 999912 (These two values, 201401 and 999912, are static)
If there are more 1 records for a CC, ex: 200 or 300, then the first record with both NULL values, should have 201401 as ValidFrom and the previous month of ValidFrom of the second record as ValidTo and second record would have 999912 as ValidTo.
The table above should look like below after the tansaction.
CC | Name | ValidFrom | ValidTo |
---|---|---|---|
100 | Business Development | 201401 | 999912 |
200 | Indirect Costs | 201401 | 201612 |
200 | Direct Costs | 201701 | 999912 |
300 | Other | 201401 | 201712 |
300 | Purchasing | 201801 | 999912 |
And if I want to see the name of the CC = 200 for the period 201610 I should see Indirect Costs and for the period 201705 I should see Direct Costs.
I hope I was clear enough.
Thanks alot & have a nice weekend,
Bilal
May be like this -
Try this... I used QlikSense
tempdata:
load * Inline [CC,Name, ValidFrom, ValidTo
100, "Business Development",,
200, "Indirect Costs", ,
200, "Direct Costs", 201701,
300, Other, ,
300, Purchasing, 201801
];
NoConcatenate
temptable2:
Load *
, If(CC<>Previous(CC), 201401,ValidFrom) As NewValidFrom;
LOAD
CC,
Name,
ValidFrom,
ValidTo
,if(CC=previous(CC),peek("Row Patition Number")+1,1) as "Row Patition Number"
RESIDENT [tempdata]
ORDER BY CC, ValidFrom Desc;
NoConcatenate
tempdata1:
Load *,Date(AddMonths( Date(Date#(Previous(NewValidFrom),'YYYYMM'),'YYYYMM'),-1), 'YYYYMM') As tempNewValidTo Resident temptable2
Order BY CC, ValidFrom asc;
Drop Tables [tempdata];
JOIN
Load
CC As CC1,
Name,
if(CC=previous(CC),peek("Row Patition Number2")+1,1) as "Row Patition Number2"
RESIDENT [temptable2]
ORDER BY CC,ValidFrom asc;
Drop table [temptable2];
;
Data:
Load *, If(([Row Patition Number]=[Row Patition Number2] AND CC=CC1) OR ValidFrom > 0, 999912, IF(CC=CC1 AND [Row Patition Number2]>1,
tempNewValidTo) ) AS NewValidTo
Resident tempdata1;
Drop Fields CC1,tempNewValidTo,[Row Patition Number],[Row Patition Number2];
Drop Table tempdata1;
Thank you Digvijay,
It worked perfectly.
Cheers,
Bilal
Glad it worked for you!