topic adding an expression into a load script in QlikView

adding an expression into a load script

Tue, 08 Mar 2011 18:23:25 GMT

I am having problems placing an expression in a load script and would appreciate any help.

I load the following table okay:

Table_1:

LOAD ID,

Item_Name,

Component_Name,

Fail_Date,

Weighting;

SQL SELECT *

FROM Database1;

There are 20 Components per Item. Each has a weighting of either 1 or 2.

I want to add into the load script the following expression that will:

Add weighting only if there is a fail date.

Then sum all the weighting for each Item returning a 1 if the total is >1 , else 0.

-(If total is over 1 then the item fails.)

( I will add date filters later to the dashboard).

I can then use the 1 and 0 to calculate number of fails etc for any given period.

if(sum(if(isnull(Fail_Date),0,(Weighting)))>1,1,0)as Item_Status

thanks in advance

john

adding an expression into a load script

nstefaniuk — Wed, 09 Mar 2011 09:40:36 GMT

Hi.

Add a second step in your load script, using the QV table loaded previously and grouping the data only by id and item. So you can calculate the total weight over all the components of each item.

adding an expression into a load script

Wed, 09 Mar 2011 11:56:33 GMT

Hi, Thanks for replying

I finally got it to work - not sure if this is the most elegant solution or best practice.

Table_1:

LOAD ID,

`Item_Name`,

`Component_Name`,

`Fail_Date`,

Weighting;

SQL SELECT *

FROM `Table_1`;

Item_Summary:

LOAD Item_Name,

if(sum(if(isnull(Fail_Date),0,(Weighting)))>1,1,0)as Item_Status

RESIDENT Table_1

GROUP BY Item_Name

adding an expression into a load script

Wed, 09 Mar 2011 16:32:24 GMT

Following on from this:

I have TableA that contains a list of Items. Each Item has one SubItem

Using Table_2 that contains the data on the Subitems, I create a similar expression - SubItem_Status

I want to add the expressions Item_Status and SubItem_Status together in the load script to create Overall_Status

I can achieve this in a straight table but cannot emulate it in the script.

Help appreciated.

John