Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have below data
Status | Open Date | Closed Date |
Closed | 4/9/2019 | 4/25/2019 |
Closed | 2/22/2019 | 2/22/2019 |
New | 2/21/2019 | |
Closed | 4/28/2019 | 5/1/2019 |
Please help me to achieve below in Qlikview using if([Closed Date] contains null or [Closed Date] is > the monthend) and [Open Date] is <= monthend ,1,0)
Status | Open Date | Closed Date | 1/31/2019 | 2/28/2019 | 3/31/2019 | 4/30/2019 |
Closed | 4/9/2019 | 4/25/2019 | 0 | 0 | 0 | 0 |
Closed | 2/22/2019 | 2/22/2019 | 0 | 0 | 0 | 0 |
New | 2/21/2019 | 0 | 1 | 1 | 1 | |
Closed | 4/28/2019 | 5/1/2019 | 0 | 0 | 0 | 1 |
1 | 1 | 2 |
Thanks In advance
Something like this could work... but can take a lot of toll on your script
Test:
LOAD Status,
Date([Open Date]) as [Open Date],
Date([Closed Date]) as [Closed Date],
Date(If(Len(Trim([Closed Date])) > 0, [Closed Date], Today())) as [Closed Date New];
LOAD * INLINE [
Status, Open Date, Closed Date
Closed, 4/9/2019, 4/25/2019
Closed, 2/22/2019, 2/22/2019
New, 2/21/2019
Closed, 4/28/2019, 5/1/2019
];
Left Join (Test)
LOAD MonthStart(MinDate, IterNo()-1) as MonthStart,
Date(Floor(MonthEnd(MonthStart(MinDate, IterNo()-1)))) as MonthEnd
While MonthStart(MinDate, IterNo()-1) <= MaxDate;
LOAD MonthStart(Min([Open Date])) as MinDate,
MonthStart(Max([Closed Date New])) as MaxDate
Resident Test;
FinalTable:
LOAD *,
If([Closed Date New] > MonthEnd and [Open Date] < MonthEnd, 1, 0) as Open
Resident Test;
DROP Table Test;
Could someone hint me to get above requirement in qV
Where is the monthend field coming from?
Thanks Sunny for your time.
"monthend" is derived field contains lastdate of the each month.
1/31/2019 | 2/28/2019 | 3/31/2019 | 4/30/2019 |
But where is this field created? in the script? And also you are looking to do this in the script, right?
Yes, want to create this field in the script
Trying to create the field in script as below and use the Open in UI expression as count(Open). But not getting expected o/p
Test:
load *,
if(([Closed Date1] ='' or [Closed Date1] > Tempdate )and [Open Date1] <= Tempdate ,1,0) as Open
;
LOAD *,
Date(MonthEnd([Closed Date])) as Tempdate,
Date([Open Date]) as [Open Date1],
Date([Closed Date]) as [Closed Date1]
;
Load * Inline [
Status, Open Date, Closed Date
Closed, 4/9/2019, 4/25/2019
Closed, 2/22/2019, 2/22/2019
New, 2/21/2019,
Closed, 4/28/2019, 5/1/2019
];
Something like this could work... but can take a lot of toll on your script
Test:
LOAD Status,
Date([Open Date]) as [Open Date],
Date([Closed Date]) as [Closed Date],
Date(If(Len(Trim([Closed Date])) > 0, [Closed Date], Today())) as [Closed Date New];
LOAD * INLINE [
Status, Open Date, Closed Date
Closed, 4/9/2019, 4/25/2019
Closed, 2/22/2019, 2/22/2019
New, 2/21/2019
Closed, 4/28/2019, 5/1/2019
];
Left Join (Test)
LOAD MonthStart(MinDate, IterNo()-1) as MonthStart,
Date(Floor(MonthEnd(MonthStart(MinDate, IterNo()-1)))) as MonthEnd
While MonthStart(MinDate, IterNo()-1) <= MaxDate;
LOAD MonthStart(Min([Open Date])) as MinDate,
MonthStart(Max([Closed Date New])) as MaxDate
Resident Test;
FinalTable:
LOAD *,
If([Closed Date New] > MonthEnd and [Open Date] < MonthEnd, 1, 0) as Open
Resident Test;
DROP Table Test;
Thanks Sunny, doing date left join with main table worked.
Super