Alternative to Set Analysis in Load Script

[Anonymous]

Hi all!

I read in http://community.qlik.com/thread/62774 that Set Analysis can not be used in Load Script, which is why I need an alternative.

The business need is to get 'point in time' reporting regarding CRM's funnel (on a daily/weekly/monthly etc level). As the MS CRM database does not keep track of the historical value of the funnel, I have to save it to a separate qvd during my backend reload to get this historical data.

I have already managed to save the amount of the whole funnel when dimension StateCode = 0 (which means open opportunities). Our Sales Manager, however, wants to view the funnel value as a stacked bar chart with different StatusCodes making the stacks in the chart (offers, prospects, won pending contracts & on holds).

So the to be data collected per reload should be DateofReload, value of offers, value of prospects, value of on-holds and value of won pending contracts with these last four summing up to the value of the whole funnel.

The code below will not reload with QlikView saying that the expression is invalid. What is wrong with the expression? Or is there a better approach to tackling this specific problem?

Funnel:
LOAD
    Date(floor(ReloadTime()), 'D.M.YYYY') as DateofReload, 
    Sum(EstimatedValue*(CloseProbability/100)) as ValueofFunnel,    // whole  value of funnel
    if (StatusCode = 200000, Sum(EstimatedValue*(CloseProbability/100))) as ValueofOffers,
    if (StatusCode = 1, Sum(EstimatedValue*(CloseProbability/100))) as ValueofProspects,
    if (StatusCode = 200008, Sum(EstimatedValue*(CloseProbability/100))) as ValueofWPCs,    // WPCs = won pending contracts
    if (StatusCode = 2, Sum(EstimatedValue*(CloseProbability/100))) as ValueofOnHolds
Resident Fact            // EstimatedValue etc. are loaded from Fact (data from Opportunity-table in CRM)
Where StateCode = 0;    // open opportunities

Many thanks,

Juho

[Gysbert_Wassenaar]

Your load statement is missing a Group By clause for StatusCode. So if you add Group By StatusCode below the Where clause it should work.

[Anonymous]

Ok, what is the correct syntax to do this? If I add it like below, the where-keyword turns to red (same color as StatusCode) and the script says 'field not found - <where>.

Resident Fact       

Group By StatusCode,

Where StateCode = 0;

[Gysbert_Wassenaar]

Try:

load

    ...etc

Resident Fact        

Where StateCode = 0 

group by StatusCode;

[Anonymous]

Stupid of me not to try it that way...

This is the result it currently saves. What do I need to do to get data of a single reload date to a single row (with date as key)?

[Gysbert_Wassenaar · Accepted solution]

Funnel:
LOAD
    Date(floor(ReloadTime()), 'D.M.YYYY') as DateofReload, 
    Sum(EstimatedValue*(CloseProbability/100)) as ValueofFunnel,    
    Sum(if(StatusCode = 200000, EstimatedValue*(CloseProbability/100))) as ValueofOffers,
    Sum(if(StatusCode = 1, EstimatedValue*(CloseProbability/100))) as ValueofProspects,
    Sum(if(StatusCode = 200008, EstimatedValue*(CloseProbability/100))) as ValueofWPCs,   
    Sum(if(StatusCode = 2, EstimatedValue*(CloseProbability/100))) as ValueofOnHolds
Resident Fact          
Where StateCode = 0;

[Anonymous]

Many thanks!