Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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

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

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