If Condition

[#1]

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

[sunny_talwar]

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

[#3]

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

[sunny_talwar]

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]

[sunny_talwar]

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

[#6]

Thanks sunny. This worked for me.

[sunny_talwar]

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