10 Replies Latest reply: Jul 13, 2015 5:06 AM by phuviet pham

Split record during load by distribution key

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.

Stijn

• Re: Split record during load by distribution key

Cost * 0.5 as Plant1Cost,

Cost * 0.2 as Plant2Cost

Cost * 0.3 as Plant3Cost

• Re: Split record during load by distribution key

Hi

I have the same Answear as Manish.

Create 3 Calculations

• Re: Split record during load by distribution key

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

• Re: Split record during load by distribution key

Provide 5 lines of sample data...

• Re: Split record during load by distribution key

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

• Re: Split record during load by distribution key

I haven't gotten exact data yet but:

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

• Re: Split record during load by distribution key
```Plant:
Load PLANT as PlantNumber, KEY/100 as KEY Inline
[
PLANT, KEY
000, 50
001,    30
002,    20
];

DIRECT:
[
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:
[
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:
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;
```
• Re: Split record during load by distribution key

Thank you.

I never used a loop in my script.

• Re: Split record during load by distribution key

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

• Re: Split record during load by distribution key

This is For Loop of QlikView Script.

I have used For Loop and Peek Function which you can read from QlikView Reference Manual.