Editing Script

[#1]

Hi,

I have a small problem for which I am not able to find a solution. I have got three different tables which gives the Cost details for the product by different cost centers.

CostCentre1:

ProductID & Cost1

CostCentre2:

ProductID & Cost2

CostCentre3:

ProductID & Cost3

CostCentre4:

ProductID & Cost4

As all the four data files are coming from different sources, I load each seprately and then join them together . So my final table looks like

CostCentre:

ProductID, Cost1, Cost2, Cost3 , Cost4

Now what I want to do is that I want to define a variable which counts how many cost centers allocated cost to the product & what was the maximum cost

Hence my final table should look like

ProductID, Cost1, Cost2, Cost3 , Cost4, #Costs, MaxCost

Pls help

[#2]

Hi,

look at my example Script:

inline:
LOAD * INLINE [
 ProductID, Cost1, Cost2, Cost3, Cost4
 1, 10, 20, 30, 40
 2, 100, 200, 300, 400
 3, 103, , 303, 403
 4, 1, 2, , 4
];

Join
Load
 ProductID,
 rangesum(
 IF(Cost1 > 0, 1),
 IF(Cost2 > 0, 1),
 IF(Cost3 > 0, 1),
 IF(Cost4 > 0, 1)) AS #Costs
Resident inline
;

Join
Load
 ProductID,
 rangemax(Cost1, Cost2, Cost3, Cost4) AS MaxCost
Resident inline
;

Regards, Roland

[#3]

Thanks Ronald,

In the answer you have given lets say I need to rename a few fields and the 1st load reads as under:

inline:
LOAD * INLINE [
ProductID as ABC,
Cost1, Cost2, Cost3, Cost4
1, 10, 20, 30, 40
2, 100, 200, 300, 400
3, 103, , 303, 403
4, 1, 2, , 4];

What will be syntax of the remaining statements?

[#4]

Could someone pls help

[#5 · Accepted solution]

Hi, replace ProductID with ABC within the next two Load - Statements. Thats all.

RR

[#6]

Load all the costs table data into a temporary table:
t1:
Load ProductId,
Cost1 as Cost
From...
Load ProductID,
Cost2 as Cost
From...

Now you can aggregate the data:
t2:
Load ProductID,
count(ProductId) as #Costs,
max(Cost) as MaxCost
Resident t1;

Drop table t1;

You now have table t2 which you can join to your other table (and then drop) or leave as is.

Regards,

Gordon

[#7]

Doesn't work that way. I am uploading a QV - pls have a look

[#8]

Thanks for all the help finally sorted out the problem