topic Re: Alternative to Set Analysis in Load Script in QlikView

Alternative to Set Analysis in Load Script

Anonymous — Fri, 05 Jul 2013 10:34:38 GMT

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

Re: Alternative to Set Analysis in Load Script

Gysbert_Wassenaar — Fri, 05 Jul 2013 10:40:12 GMT

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.

Re: Alternative to Set Analysis in Load Script

Anonymous — Fri, 05 Jul 2013 10:55:03 GMT

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;

Re: Alternative to Set Analysis in Load Script

Gysbert_Wassenaar — Fri, 05 Jul 2013 11:47:58 GMT

Try:

load

...etc

Resident Fact

Where StateCode = 0

group by StatusCode;

Re: Alternative to Set Analysis in Load Script

Anonymous — Fri, 05 Jul 2013 12:12:08 GMT

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

Re: Alternative to Set Analysis in Load Script

Gysbert_Wassenaar — Sat, 06 Jul 2013 08:26:54 GMT

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;

Re: Alternative to Set Analysis in Load Script

Anonymous — Mon, 08 Jul 2013 06:40:24 GMT

Many thanks!