Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Editing Script

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

1 Solution

Accepted Solutions
Not applicable

Editing Script

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

RR

View solution in original post

7 Replies
Not applicable

Editing Script

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

Not applicable

Editing Script

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?

Not applicable

Editing Script

Could someone pls help

Not applicable

Editing Script

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

RR

View solution in original post

Not applicable

Editing Script

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

Not applicable

Editing Script

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

Not applicable

Editing Script

Thanks for all the help finally sorted out the problem Big Smile