adding an expression into a load script

[#1]

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

[nstefaniuk]

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.

[#3]

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

[#4]

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