Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi, replace ProductID with ABC within the next two Load - Statements. Thats all.
RR
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
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?
Could someone pls help
Hi, replace ProductID with ABC within the next two Load - Statements. Thats all.
RR
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
Doesn't work that way. I am uploading a QV - pls have a look
Thanks for all the help finally sorted out the problem