Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
bilalgunay
Contributor III
Contributor III

Updating some null values with a defined logic

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.

CCNameValidFromValidTo
100Business DevelopmentNULLNULL
200Indirect CostsNULLNULL
200Direct Costs201701NULL
300OtherNULLNULL
300Purchasing201801NULL

   

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.

CCNameValidFromValidTo
100Business Development201401999912
200Indirect Costs201401201612
200Direct Costs201701999912
300Other201401201712
300Purchasing201801999912

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

1 Solution

Accepted Solutions
Digvijay_Singh

May be like this -

Capture.PNG

View solution in original post

4 Replies
Digvijay_Singh

May be like this -

Capture.PNG

jerifortune
Creator III
Creator III

Try this... I used QlikSense

updating value.PNG

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
Contributor III
Contributor III
Author

Thank you Digvijay,

It worked perfectly.

Cheers,

Bilal

Digvijay_Singh

Glad it worked for you!