topic Re: Fieldvalue or Firstsortedvalue instead of LEFT JOIN in QlikView

Fieldvalue or Firstsortedvalue instead of LEFT JOIN

Wed, 18 Feb 2015 14:22:21 GMT

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

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

Br Johan

Re: Fieldvalue or Firstsortedvalue instead of LEFT JOIN

hic — Wed, 18 Feb 2015 14:37:41 GMT

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

Re: Fieldvalue or Firstsortedvalue instead of LEFT JOIN

Thu, 19 Feb 2015 10:25:43 GMT

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

Re: Fieldvalue or Firstsortedvalue instead of LEFT JOIN

hic — Thu, 19 Feb 2015 13:25:55 GMT

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