Count

[priya945]

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

[priya945]

Could someone hint me to get above requirement in qV

[sunny_talwar]

Where is the monthend field coming from?

[priya945]

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

[sunny_talwar]

But where is this field created? in the script? And also you are looking to do this in the script, right?

[priya945]

Yes, want to create this field in the script 

[priya945]

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

];

[sunny_talwar · Accepted solution]

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;

[priya945]

Thanks Sunny, doing  date left join with main table worked.

[sunny_talwar]

Super