Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello all,
I have a new question. We want to compare our costs versus our income.
Tthe company has several plants. And some costs can't be addresses to one plant.
So they created a distribution key that counts for all costs that can't be addresses to one specific plant.
for example:
We have a cost of €1000.
plant 1 gets 50% = €500
plant 2 gets 20% = €200
plant 3 gets 30% = €300
Can I split this up during the load of Qlikview?
In stead of having 1 record of €1000, I want three records.
thank you for your help.
Stijn
Plant:
Load PLANT as PlantNumber, KEY/100 as KEY Inline
[
PLANT, KEY
000, 50
001, 30
002, 20
];
DIRECT:
Load * Inline
[
FIRM, PLANT, NUMBER, DATE, COST
1, 000, 123, 20150713, 100
1, 001, 456, 20150712, 200
1, 002, 789, 20150711, 300
1, 000, 159, 20150711, 500
];
NoConcatenate
INDIRECT:
Load * Inline
[
FIRM, PLANT, NUMBER, DATE, COST
1, , 357, 20510709, 2000
];
Let vPLANT = FieldValueCount('PlantNumber');
For i= 0 to vPLANT-1
Let vPlantName = Peek('PlantNumber',$(i),'Plant');
Let vKey = Peek('KEY',$(i),'Plant');
NoConcatenate
NEWTABLE:
Load
FIRM,
'$(vPlantName)' as PLANT,
NUMBER,
DATE,
COST * $(vKey) as COST
Resident INDIRECT;
Next i
Drop Table INDIRECT;
Concatenate (DIRECT) Load * Resident NEWTABLE;
Drop Table NEWTABLE;
Cost * 0.5 as Plant1Cost,
Cost * 0.2 as Plant2Cost
Cost * 0.3 as Plant3Cost
Hi
I have the same Answear as Manish.
Create 3 Calculations
Hello,
thank you for the quick reply.
Doesn't this mean I get 1 record with a field for every plant?
I need to combine these lines with lines that can be assigned to a plant.
So every records has a plant and a cost. (Other things as well off course)
My result of these lines need to look the same. Every records has 1 plant and 1 cost.
kind regards
stijn
Provide 5 lines of sample data...
i think you should paint what you want exactly. Its better to think how to do it
I haven't gotten exact data yet but:
Data to load
DISTRIBUTION KEY:
PLANT KEY
000 50
001 30
002 20
DIRECT data
FIRM PLANT NUMBER DATE COST
1 000 123 20150713 100
1 001 456 20150712 200
1 002 789 20150711 300
1 000 159 20150711 500
INDIRECT data
FIRM PLANT NUMBER DATE COST
1 357 20510709 2000
RESULT
FIRM PLANT NUMBER DATE COST
1 000 123 20150713 100
1 001 456 20150712 200
1 002 789 20150711 300
1 000 159 20150711 500
1 000 357 20150709 1000
1 001 357 20150709 600
1 002 357 20150709 400
Plant:
Load PLANT as PlantNumber, KEY/100 as KEY Inline
[
PLANT, KEY
000, 50
001, 30
002, 20
];
DIRECT:
Load * Inline
[
FIRM, PLANT, NUMBER, DATE, COST
1, 000, 123, 20150713, 100
1, 001, 456, 20150712, 200
1, 002, 789, 20150711, 300
1, 000, 159, 20150711, 500
];
NoConcatenate
INDIRECT:
Load * Inline
[
FIRM, PLANT, NUMBER, DATE, COST
1, , 357, 20510709, 2000
];
Let vPLANT = FieldValueCount('PlantNumber');
For i= 0 to vPLANT-1
Let vPlantName = Peek('PlantNumber',$(i),'Plant');
Let vKey = Peek('KEY',$(i),'Plant');
NoConcatenate
NEWTABLE:
Load
FIRM,
'$(vPlantName)' as PLANT,
NUMBER,
DATE,
COST * $(vKey) as COST
Resident INDIRECT;
Next i
Drop Table INDIRECT;
Concatenate (DIRECT) Load * Resident NEWTABLE;
Drop Table NEWTABLE;
Thank you.
I never used a loop in my script.
I can start with this.
Hi Manish,
What the Name of your SQL code?. Is that P SQL?.
´Because it quite new to me and i have only Knowledege of T-SQL. But your Code is not like T-SQL.
Another Question: How to learn your SQL?. I mean is there any Reference, or book?
Thanks