topic Where exists and group by in QlikView

Where exists and group by

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

Am I writing the below script correctly?

I am following on from a previous script that was a Fiscal Year Month which has a 'where exists group by' clause which also works. Mine however for the Fiscal Year Week does not work

// Structure load - Monthly
temp_Weekly_Validations:
Load
[Country Code]
Resident Country;

Outer Join(temp_Weekly_Validations)
Load
[Fiscal Year Week],
null() as Date

Resident [Weekly Calendar];

Outer Join(temp_Weekly_Validations)
Load
Weekly_Control,
0 as [Record Count],
0 as [Value Sum]

Resident Weekly_Controls
where Weekly_Control <> 'Avaya';

Weekly_Validations:
NoConcatenate
Load
*,
Weekly_Control & '-' & [Country Code] & '-' & [Fiscal Year Week] as %ValidationKey

Resident temp_Weekly_Validations;

drop table temp_Weekly_Validations;

// Structure load - Daily
temp_Weekly_Validations:
Load
[Country Code] as [Country Code2]

Resident Country;

Outer Join(temp_Weekly_Validations)
Load
null() as [Fiscal Week2],
Date as Date2

Resident [Daily Calendar];

Outer Join(temp_Weekly_Validations)
Load
Weekly_Control as Weekly_Control2,
0 as [Record Count2],
0 as [Value Sum2]

Resident Weekly_Controls
where Weekly_Control = 'Avaya';

Weekly_Validations:
Load [Country Code2] as [Country Code],
[Fiscal Week2] as [Fiscal Year Week],
Date2 as Date,
Weekly_Control2 as Weekly_Control,
[Record Count2] as [Record Count],
[Value Sum2] as [Value Sum],
Weekly_Control2 & '-' & [Country Code2] & '-' & Date2 as %ValidationKey
Resident temp_Weekly_Validations;

drop table temp_Weekly_Validations;

Concatenate(Weekly_Validations)
LOAD
right(left(PurgeChar(%ClaimMeasuresKey,'-'),7),2) as [Country Code],
%ClaimMeasuresKey,
text(ApplyMap('MapDateToFiscalWeek', PurgeChar(Weekname(Date(left(PurgeChar(%ClaimMeasuresKey,'-'),5), 'YYYYMM')), '/'))) as [Fiscal Year Week],
null() as Date,
'Claims Registered' as Weekly_Control,
count(reg_count) as [Record Count],
0 as [Value Sum]
FROM
[$(vQVDs)\A_Claim_Measures.qvd]
(qvd)
where exists([Fiscal Year Week], text(ApplyMap('MapDateToFiscalWeek', PurgeChar(Weekname(Date(left(PurgeChar(%ClaimMeasuresKey,'-'),5), 'YYYYMM')), '/'))))
group by text(ApplyMap('MapDateToFiscalWeek', PurgeChar(Weekname(Date(left(PurgeChar(%ClaimMeasuresKey,'-'),5), 'YYYYMM')), '/'))), Subfield(%ClaimMeasuresKey, '-', 1);

My Fiscal Year Week appears as

201737

201736

201735 etc

Let vWeeklyStart = num(WeekStart(Today(), -13)); // 13 months back
Let vWeeklyEnd = num(WeekStart(Today(), -1)); // Last Month

[Weekly Calendar]:
LOAD distinct
Fiscal_Year_Week as [Fiscal Year Week],
Fiscal_Year & Fiscal_Week as [Fiscal Week]
FROM
[$(vQVDs)\T_Calendar_Test.qvd]
(qvd)
where num(Date) >= $(vWeeklyStart)
and num(Date) <= $(vWeeklyEnd);

Re: Where exists and group by

sunny_talwar — Wed, 20 Sep 2017 11:39:44 GMT

May be move the Group By statement to the preceding load... something along these lines

Concatenate(Weekly_Validations)

LOAD [Country Code],

%ClaimMeasuresKey,

[Fiscal Year Week],

null() as Date,

'Claims Registered' as Weekly_Control,

0 as [Value Sum],

count(reg_count) as [Record Count]

Group By [Country Code], %ClaimMeasuresKey, [Fiscal Year Week];

LOAD
right(left(PurgeChar(%ClaimMeasuresKey,'-'),7),2) as [Country Code],
%ClaimMeasuresKey,
text(ApplyMap('MapDateToFiscalWeek', PurgeChar(Weekname(Date(left(PurgeChar(%ClaimMeasuresKey,'-'),5), 'YYYYMM')), '/'))) as [Fiscal Year Week],

reg_count

FROM
[$(vQVDs)\A_Claim_Measures.qvd]
(qvd)
where exists([Fiscal Year Week], text(ApplyMap('MapDateToFiscalWeek', PurgeChar(Weekname(Date(left(PurgeChar(%ClaimMeasuresKey,'-'),5), 'YYYYMM')), '/'))));

Re: Where exists and group by

bobbydave — Wed, 20 Sep 2017 14:43:44 GMT

Let me try this and come back to u

Re: Where exists and group by

bobbydave — Thu, 21 Sep 2017 11:33:25 GMT

Hi Sunny,

I've attached the qvw.

If you look at the month, there is a where exists/group by and this works fine.

However, I am trying to do the same thing with the week, using the where exists/group by and it does not work.

In my expression, it show use sum, not count in the weekly like it does in the monthly expression and the monthly expression using sum, does equate to the correct number.

When i try to look at the sum of Weekly Record Count, I get 0 but when i do a count, i get a value of 1.

It works perfectly fine for the Monthly Record Count.

I believe it is a result of the Where Exists/Group by in the Weekly tab.

Any help appreciated

Re: Where exists and group by

bobbydave — Thu, 21 Sep 2017 13:12:06 GMT

Any ideas you guys?