Where exists and group by

[bobbydave]

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);

[sunny_talwar]

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')), '/'))));

[bobbydave]

Let me try this and come back to u

[bobbydave]

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

[bobbydave]

Any ideas you guys?