topic Re: Help with a Load Question in QlikView

Help with a Load Question

Tue, 17 Dec 2013 15:17:03 GMT

Our current process is we have created a chart that has 22 KPI's. What we do in the chart dimension is we change our 7 markets to 4. Here is how we do it:

Industry
=if(Industry= 'A', '1',
if(Industry = 'B', '1',
if(Industry = 'C,1',
if(Industry = 'D', '2',
if(Industry = 'E', '3',
if(Industry = 'F','3',
if(Industry = 'G', '4'))))))),

Then in the expression we have various calculations similar to the ones listed below:

1. Fractile(total <Industry> aggr(sum({<AssetName= {"Product Name"}>}EF1Value__c),AcctNum, Industry),.80)

2. median(Total <Industry>aggr(sum({<AssetName= {"Product Name"}>}EF1Value__c),AcctNum))

From there we export the data out to an excel file with a row that has:

AcctNum, Industry, KPI1, KPI2, etc.

Then we send it over so our DB cant take it and do a process that loads the information into a sql db and then push that information up to a dashboard.

I know this is Set Analysis cant be done in a load statement but is there any way to accomplish the expression above in a load statement so I can then store the results in a csv file so our sql database can read it. We want the script to perform the heavy lifting..

Any ideas would be good and if there is not a way that is ok as well.

David

Re: Help with a Load Question

Anonymous — Tue, 17 Dec 2013 15:28:53 GMT

Hi David,

I think there might be several approaches to the first part of this.

1. Do a Mapping Load from an inline table to match the A,B,C to the 1,2,3 in a new field

2. Do a left join using an inline table

3. A Case statement, but I am not too familiar with these.

Once you have the table with the correct industry labels you can then probably use a Where clause in a resident load to deal with the set analysis bit. I am not sure if the fractile and median functions work the same way in the load script but the QV help files should make that clearer.

Re: Help with a Load Question

Tue, 17 Dec 2013 15:29:28 GMT

Once you have the industry labels in your fact table. You'll need to do it over several load statements. Start with the inner-most Sum() then build on it in the next LOAD/RESIDENT statement.

Example:

Table1:

LOAD AcctNum,

Industry,

Sum(EF1Value__c) as InnerSum

RESIDENT [your fact table]

WHERE AssetName = 'Product Name'

GROUP BY AcctNum, Industry

;

Table2:

NOCONCATENATE LOAD

Industry,

Fractile(InnerSum, .80)

RESIDENT Table1

GROUP BY Industry

;

Table2 should have the values that your listed function would have. You can do a similar method with the Median statement as well.

Re: Help with a Load Question

Tue, 17 Dec 2013 15:32:27 GMT

As Adam said, you can either map the Industry labels over or just add an if statement into your script.

if(Industry= 'A', '1',
     if(Industry = 'B', '1',
     if(Industry = 'C,1',
     if(Industry = 'D', '2',
     if(Industry = 'E', '3',
     if(Industry = 'F','3',
     if(Industry = 'G', '4'

))))))) as IndustryNormalized,

Re: Help with a Load Question

Tue, 17 Dec 2013 19:30:46 GMT

Hi Matt,

I am able to get a Facts Table that has:

AcctNum, Industry, NewIndustry, etc.

When I try to perform the first load statement above I get synethic keys on acctnum and NewIndustry. Any reason for that?

David

Re: Help with a Load Question

Wed, 18 Dec 2013 15:01:53 GMT

Yes, QV does this automatically when at least two tables share two or more fields. Since QV relies on a single field for a key it creates a synthetic key that references a new key table that links the tables.

Once you are finished with Table1 in the load script add: DROP TABLE Table1;

This will remove the unnecessary table and avoid the synthetic key. Table2 shouldn't create a synthetic key since it only has 1 shared field between the original table.

Side note: A synthetic key isn't necessarily a bad thing, but it is generally preferred to concatenate all the key fields together to create a single key field.

For example:

Table:

LOAD AcctNm & NewIndustry as %Key_AcctIndustry

.... rest of load statement ...

In your fact table you would need to create this same key.

Re: Help with a Load Question

Wed, 18 Dec 2013 15:39:26 GMT

You can do these calculations in script level by Group By clause and other aggregation functions, but the selections will be limited.

Please post sample source data , calculation requirements and desired out put to help more.

Re: Help with a Load Question

Wed, 18 Dec 2013 16:38:06 GMT

Matt---

That makes sense. I have run into that before and have taken care of it like you stated. I am relatively new to Qlikview and still learning some things. Do you know if the Fractile command works the same in a chart as it does in a load statement? The other thing that was mentioned was a Case statement. I have not done one of those yet and any suggestions on learning the syntax.

The other question I have is can we put the following information into a load statement? If we can that would be awesome.

=if(Aggr(count(WOID),AcctNum)= 0,0,

aggr(IF(count({<StatusDesc = {"Complete", "Closed*"}>}WOID)/Students__c < .1,0,0),AcctNum)+

aggr(IF(count({<StatusDesc = {"Complete", "Closed*"}>}WOID)/Students__c >= .1,1,0),AcctNum)

+aggr(IF(count({<StatusDesc = {"Complete", "Closed*"}>}WOID)/Students__c >= .3,1,0),AcctNum)

+aggr(IF(count({<StatusDesc = {"Complete", "Closed*"}>}WOID)/Students__c >= .4,1,0),AcctNum)

+aggr(IF(count({<StatusDesc = {"Complete", "Closed*"}>}WOID)/Students__c >= .6,1,0),AcctNum)

+aggr(IF(sum({<StatusDesc = {"Complete", "Closed*"}>}ActualHours)/count({<StatusDesc = {"Complete", "Closed*"}>}WOID) >= .5,1,0),AcctNum))

Just trying to let the script do the heavy lifting as much as possible. Is that a best practice? Can run these scripts to run at night and be ready the next day.

Thanks for you help.

David

Re: Help with a Load Question

Wed, 18 Dec 2013 17:11:53 GMT

Hi There.

Here is what I posted above and what the current setup is. We are doing all the calculations at the Chart Level but we would love to move as much to the load statement as possible. Here you go.

The current process is we run various scripts that takes our product data and Salesforce data to come up with a set of KPI's for each account. The data we pull is just raw data. An example would be:

AcctNum, Number of Work Orders, etc. Once we have the raw data we have a chart that has all the calculations in it. The example of the chart would be:

Account Number, Field 1, Field 2, Field 3, etc. One of the calculations is:

=if(Aggr(count(WOID),AcctNum)= 0,0,

aggr(IF(count({<StatusDesc = {"Complete", "Closed*"}>}WOID)/Students__c < .1,0,0),AcctNum)+

aggr(IF(count({<StatusDesc = {"Complete", "Closed*"}>}WOID)/Students__c >= .1,1,0),AcctNum)

+aggr(IF(count({<StatusDesc = {"Complete", "Closed*"}>}WOID)/Students__c >= .3,1,0),AcctNum)

+aggr(IF(count({<StatusDesc = {"Complete", "Closed*"}>}WOID)/Students__c >= .4,1,0),AcctNum)

+aggr(IF(count({<StatusDesc = {"Complete", "Closed*"}>}WOID)/Students__c >= .6,1,0),AcctNum)

+aggr(IF(sum({<StatusDesc = {"Complete", "Closed*"}>}ActualHours)/count({<StatusDesc = {"Complete", "Closed*"}>}WOID) >= .5,1,0),AcctNum))

Once we have that at the account level we export the data to an excel file. We use the Salesforce Data Loader to upload the information by account and store those numbers in various fields. We have another script that reads those numbers at the account level out of Salesforce so we can combine same industries together to get a list of 22 KPI's by Fractile and Median. Then we post those KPI's to a dashboard so our clients can compare themselves to their peers. So we have a set of KPI's that are at an Account level so they can see where they are and then 2 other sets of KPI's so they can compare themselves to their peers. I hope this makes sense.

Here is what is at the Industry level Chart:

Industry
=if(Industry= 'A', '1',
if(Industry = 'B', '1',
if(Industry = 'C,1',
if(Industry = 'D', '2',
if(Industry = 'E', '3',
if(Industry = 'F','3',
if(Industry = 'G', '4'))))))),

Then in the expression we have various calculations similar to the ones listed below:

1. Fractile(total <Industry> aggr(sum({<AssetName= {"Product Name"}>}EF1Value__c),AcctNum, Industry),.80)

2. median(Total <Industry>aggr(sum({<AssetName= {"Product Name"}>}EF1Value__c),AcctNum))

From there we export the data out to an excel file with a row that has:

AcctNum, Industry, KPI1, KPI2, etc.

I would love to move calculations at the account level to a script load and the same at the industry level. Let me know your thoughts.

David

Re: Help with a Load Question

Wed, 18 Dec 2013 17:32:21 GMT

Putting heavy calculations into the load script is always a good idea. You just need to make sure that user selections will not cause your script values to become invalid. (I cant think of an example, but its a definite possibility).

I dont know if Fractile is the same, but I don't see why it wouldn't be. You can try it both ways and confirm the numbers.

You cannot use the aggr() function in a load script. Aggr() is basically like Table1 and Table2 I posted earlier. First, you would create a table to calculate the values of the inside function, then load into a second table for the outside function. Plus, you will need to add the chart dimension.

In your case, I don't think that's the best use of aggr(). What are you trying to accomplish with that expression?

Re: Help with a Load Question

Wed, 18 Dec 2013 18:32:52 GMT

What I am trying to accomplish is give an account a score from 1 to 5. I count all the work orders over a rolling 12 month period with a status of complete and closed. Take that number and divide by the number of students for that account. If they meet the criteria of .1 work orders per student they get 1 point, .3 they get two points, etc until they get to 4 points. In order to get the last point they have to track hours on their work orders. If they do and the calculation is .5 or higher they get the final point. Does this make sense?