Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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);
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');
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