Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Fieldvalue or Firstsortedvalue instead of LEFT JOIN

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

1 Solution

Accepted Solutions
Henric_Cronström
Not applicable

Re: Fieldvalue or Firstsortedvalue instead of LEFT JOIN

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

3 Replies
Henric_Cronström
Not applicable

Re: Fieldvalue or Firstsortedvalue instead of LEFT JOIN

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

Not applicable

Re: Fieldvalue or Firstsortedvalue instead of LEFT JOIN

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

Henric_Cronström
Not applicable

Re: Fieldvalue or Firstsortedvalue instead of LEFT JOIN

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