Updating some null values with a defined logic

[bilalgunay]

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

[Digvijay_Singh · Accepted solution]

May be like this -

[jerifortune]

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;

[bilalgunay]

Thank you Digvijay,

It worked perfectly.

Cheers,

Bilal

[Digvijay_Singh]

Glad it worked for you!