topic Re: Updating some null values with a defined logic in QlikView

Updating some null values with a defined logic

bilalgunay — Fri, 06 Apr 2018 15:25:15 GMT

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

Re: Updating some null values with a defined logic

Digvijay_Singh — Fri, 06 Apr 2018 17:35:29 GMT

May be like this -

Re: Updating some null values with a defined logic

jerifortune — Sat, 07 Apr 2018 17:46:06 GMT

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;

Re: Updating some null values with a defined logic

bilalgunay — Mon, 09 Apr 2018 15:50:38 GMT

Thank you Digvijay,

It worked perfectly.

Cheers,

Bilal

Re: Updating some null values with a defined logic

Digvijay_Singh — Mon, 09 Apr 2018 15:58:19 GMT

Glad it worked for you!