Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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.

thank you for your help.

Stijn

1 Solution

Accepted Solutions
MVP
MVP

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:

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;

10 Replies
MVP
MVP

Re: Split record during load by distribution key

Cost * 0.5 as Plant1Cost,

Cost * 0.2 as Plant2Cost

Cost * 0.3 as Plant3Cost

Not applicable

Re: Split record during load by distribution key

Hi

I have the same Answear as Manish.

Create 3 Calculations

Not applicable

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

MVP
MVP

Re: Split record during load by distribution key

Provide 5 lines of sample data...

Not applicable

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

Not applicable

Re: Split record during load by distribution key

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

MVP
MVP

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:

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;

Not applicable

Re: Split record during load by distribution key

Thank you.

I never used a loop in my script.

I can start with this.

Not applicable

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

Community Browser