Split record during load by distribution key

[#1]

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

[MK_QSL]

Cost * 0.5 as Plant1Cost,

Cost * 0.2 as Plant2Cost

Cost * 0.3 as Plant3Cost

[#3]

Hi

I have the same Answear as Manish.

Create 3 Calculations

[#4]

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

[MK_QSL]

Provide 5 lines of sample data...

[#6]

i think you should paint what you want exactly. Its better to think how to do it

[#7]

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

[MK_QSL · Accepted solution]

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;

[#9]

Thank you.

I never used a loop in my script.

I can start with this.

[#10]

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