topic Re: Count in QlikView

Count

priya945 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Count

priya945 — Tue, 04 Feb 2020 10:32:18 GMT

Could someone hint me to get above requirement in qV

Re: Count

sunny_talwar — Tue, 04 Feb 2020 12:51:48 GMT

Where is the monthend field coming from?

Re: Count

priya945 — Tue, 04 Feb 2020 14:02:25 GMT

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

Re: Count

sunny_talwar — Tue, 04 Feb 2020 14:08:58 GMT

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

Re: Count

priya945 — Tue, 04 Feb 2020 15:20:48 GMT

Yes, want to create this field in the script

Re: Count

priya945 — Wed, 05 Feb 2020 07:15:52 GMT

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

];

Re: Count

sunny_talwar — Wed, 05 Feb 2020 13:33:17 GMT

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;

Re: Count

priya945 — Thu, 06 Feb 2020 12:21:23 GMT

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

Re: Count

sunny_talwar — Thu, 06 Feb 2020 12:58:13 GMT

Super