topic Re: If Condition in QlikView

If Condition

Wed, 10 Aug 2016 07:34:53 GMT

Hi All,

I have a scenario here, where there are 2 columns by name End Date and Report Due Date. If the Report Due Date is Null, then I need to 7 days to the End Date column and replace the value in the Report Due Date.

Can someone help me in this regard?

Avinash

Re: If Condition

sunny_talwar — Wed, 10 Aug 2016 07:44:37 GMT

Need to subtract or add? May be this:

LOAD [End Date],

Date(Alt([Report Due Date], [End Date] + 7)) as [Report Due Date]

FROM ....

I assumed End Date + 7 days, but if it is subtract, then do End Date - 7

Re: If Condition

Wed, 10 Aug 2016 08:04:42 GMT

Hi ,

I guess u misread the question. Attaching the sample file so that you can see how i need the output to be.Two columns are mentioned that is End Date and Revenue due date.

If the revenue due date column is null then update that null value as End date+7. How to do this?

INPUT

End Date	Report due date
6/29/2016	7/6/2016
5/19/2016	5/26/2016
7/13/2016	7/16/2016
5/10/2016
7/13/2016	7/16/2016

OUTPUT SHOULD BE AS BELOW

End Date	Report due date
6/29/2016	7/6/2016
5/19/2016	5/26/2016
7/13/2016	7/16/2016
5/10/2016	5/17/2016
7/13/2016	7/16/2016

Re: If Condition

sunny_talwar — Wed, 10 Aug 2016 08:08:00 GMT

I think I did not miss read it... If Report due date is truly null, this should work,

Date(Alt([Report Due Date], [End Date] + 7)) as [Report Due Date]

If there is white space, you can try this:

Date(If(Len(Trim([Report Due Date])) > 0, [Report Due Date], [End Date] + 7)) as [Report Due Date]

Re: If Condition

sunny_talwar — Wed, 10 Aug 2016 08:13:05 GMT

Check this sample:

Table:

LOAD [End Date],

Date(Alt([Report due date], [End Date] + 7)) as [Report due date];

LOAD [End Date],

If(Len(Trim([Report due date])) > 0, [Report due date]) as [Report due date];

LOAD * Inline [

End Date, Report due date

6/29/2016, 7/6/2016

5/19/2016, 5/26/2016

7/13/2016, 7/16/2016

5/10/2016,

7/13/2016, 7/16/2016

];

I had to carry out the red line because inline table created a white space instead of true null for the 4th row. If you know that your date contains null, you won't need to use that and can move to the next load directly. Else you can just use the other alternative (Len(Trim()) > 0....)

Output in table viewer

Re: If Condition

Wed, 10 Aug 2016 08:24:49 GMT

Thanks sunny. This worked for me.

Re: If Condition

sunny_talwar — Wed, 10 Aug 2016 08:43:07 GMT

Awesome, I am glad it finally worked. Please consider closing this thread down by marking correct answer if you got what you were looking for.

Qlik Community Tip: Marking Replies as Correct or Helpful‌

Best,

Sunny