Fieldvalue or Firstsortedvalue instead of LEFT JOIN

[#1]

Hi,

I'm trying to optimize my transformation and would appreciate some pointers how to approach this:

What I'm aiming for is to make this quicker in QV and I would like to have all logic in the transformation layer.

SALES_FACT:

LOAD

     num(SalesFactId) AS %SalesFactId,

     /*Dimension ID*/

     num(D_CONTRACT_ID) AS %D_CONTRACT_ID,

     num(DATE#(D_EVENT_DATE_ID,'YYYYMMDD')) AS %D_EVENT_DATE_ID,

     if(EventTypeCd = 'INSURANCEANNULATED', 1,0) AS FlagINSURANCEANNULATED,

     if(EventTypeCd = 'INSURANCECANCELLED', 1,0) AS FlagINSURANCECANCELLED,

     if(EventTypeCd = 'INSURANCEOFFER', 1,0) AS FlagTempINSURANCEOFFER,

     if(EventTypeCd = 'INSURANCESOLD', 1,0) AS FlagTempINSURANCESOLD,

     if(MATCH([CustomerFeedback], 'DECLINED', 'ACCEPTED', 'NEVER'),1,0) AS FlagCustomerFeedbackNoCallingList,

FROM

SALES_FACT.qvd (qvd);

LEFT JOIN (SALES_FACT)

LOAD %SalesFactId,

     1 as FlagNoCallingList

RESIDENT SALES_FACT

WHERE

FlagCustomerFeedbackNoCallingList = 1 OR FlagTempINSURANCESOLD = 1 OR FlagINSURANCEANNULATED = 1 OR FlagINSURANCECANCELLED = 1;

LEFT JOIN (SALES_FACT)

LOAD max(%SalesFactId) AS %SalesFactId,

     1 as FlagINSURANCEOFFER

RESIDENT SALES_FACT

where FlagTempINSURANCEOFFER = 1

group by %D_CONTRACT_ID;

The reason for creating FlagNoCallingList is to use one simple set analysis in the dashboard to exclude those contracts that have FlagNoCallingList = 1

LOAD max(%SalesFactId) AS %SalesFactId,

     1 as FlagINSURANCEOFFER

RESIDENT SALES_FACT

where FlagTempINSURANCEOFFER = 1

group by %D_CONTRACT_ID;

is because we have duplicates in the source table (we are working on removing these) and I always want the latest record for FlagTempINSURANCEOFFER for each %D_CONTRACT_ID.

So is it possible to replace these logic with smarter scripting in QV. 

Maybe:

load

  aggr(max(fieldvalue('%SalesFactId', recno())),%D_CONTRACT_ID) 

AutoGenerate FieldValueCount('%SalesFactId');

or

FIRSTSORTEDVALUE (??, aggr(max(%SalesFactId),%D_CONTRACT_ID) )

Br Johan

[hic]

You should avoid the joins.

The script below does the same - I think.

HIC

// Find highest SalesFactID
MaxSalesFactId:
LOAD
max(SalesFactId) AS MaxSalesFactId
FROM SALES_FACT.qvd (qvd)
Where EventTypeCd = 'INSURANCEOFFER';

Let vMaxSalesFactId = Peek('MaxSalesFactId',-1,'MaxSalesFactId');

//Load Fact table
SALES_FACT:
Load *,
If(%SalesFactId = $(vMaxSalesFactId),1,0) as FlagINSURANCEOFFER,
If(FlagCustomerFeedbackNoCallingList = 1 OR FlagTempINSURANCESOLD = 1 OR
FlagINSURANCEANNULATED = 1 OR FlagINSURANCECANCELLED = 1,
1, 0) as FlagNoCallingList;
LOAD
num(SalesFactId) AS %SalesFactId,
/*Dimension ID*/
num(D_CONTRACT_ID) AS %D_CONTRACT_ID,
num(DATE#(D_EVENT_DATE_ID,'YYYYMMDD')) AS %D_EVENT_DATE_ID,
if(EventTypeCd = 'INSURANCEANNULATED', 1,0) AS FlagINSURANCEANNULATED,
if(EventTypeCd = 'INSURANCECANCELLED', 1,0) AS FlagINSURANCECANCELLED,
if(EventTypeCd = 'INSURANCEOFFER', 1,0) AS FlagTempINSURANCEOFFER,
if(EventTypeCd = 'INSURANCESOLD', 1,0) AS FlagTempINSURANCESOLD,
if(MATCH([CustomerFeedback], 'DECLINED', 'ACCEPTED', 'NEVER'),1,0) AS FlagCustomerFeedbackNoCallingList,
FROM SALES_FACT.qvd (qvd);

[#3]

Hi Henric, I would like to avoid joins but in this case the MaxSalesFactId will just generate one value for one %D_CONTRACT_ID (Agreement) right? I need the max value for each %D_CONTRACT_ID, so if I do a group by %D_CONTRACT_ID it will be provided. But then I can't use variables right?

Johan

--------------------------------------------------------------------------------------------

You should avoid the joins.

The script below does the same - I think.

HIC

// Find highest SalesFactID
MaxSalesFactId:
LOAD
max(SalesFactId) AS MaxSalesFactId
FROM SALES_FACT.qvd (qvd)
Where EventTypeCd = 'INSURANCEOFFER';

Let vMaxSalesFactId = Peek('MaxSalesFactId',-1,'MaxSalesFactId');

[hic · Accepted solution]

If you want the max value for each ContractID, then you can use a join, yes. A variable can only have one value. But then you need ContractID as key in your table - which you don't in your example above.

LOAD

     %D_CONTRACT_ID,

     Max(%SalesFactId) AS %SalesFactId,

     1 as FlagINSURANCEOFFER

     RESIDENT SALES_FACT

     where FlagTempINSURANCEOFFER = 1

     group by %D_CONTRACT_ID;

HIC