topic Re: Split record during load by distribution key in QlikView

Split record during load by distribution key

Mon, 13 Jul 2015 08:59:59 GMT

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

Re: Split record during load by distribution key

MK_QSL — Mon, 13 Jul 2015 09:04:21 GMT

Cost * 0.5 as Plant1Cost,

Cost * 0.2 as Plant2Cost

Cost * 0.3 as Plant3Cost

Re: Split record during load by distribution key

Mon, 13 Jul 2015 09:06:26 GMT

I have the same Answear as Manish.

Create 3 Calculations

Re: Split record during load by distribution key

Mon, 13 Jul 2015 09:08:02 GMT

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

MK_QSL — Mon, 13 Jul 2015 09:11:03 GMT

Provide 5 lines of sample data...

Re: Split record during load by distribution key

Mon, 13 Jul 2015 09:15:20 GMT

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

Mon, 13 Jul 2015 11:12:36 GMT

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

Re: Split record during load by distribution key

MK_QSL — Mon, 13 Jul 2015 11:31:20 GMT

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;

Re: Split record during load by distribution key

Mon, 13 Jul 2015 11:38:42 GMT

Thank you.

I never used a loop in my script.

I can start with this.

Re: Split record during load by distribution key

Mon, 13 Jul 2015 11:39:00 GMT

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

MK_QSL — Mon, 13 Jul 2015 11:50:30 GMT

This is For Loop of QlikView Script.

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