Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
MK_QSL
MVP
MVP

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;

View solution in original post

10 Replies
MK_QSL
MVP
MVP

Cost * 0.5 as Plant1Cost,

Cost * 0.2 as Plant2Cost

Cost * 0.3 as Plant3Cost

Not applicable
Author

Hi

I have the same Answear as Manish.

Create 3 Calculations

Not applicable
Author

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
MVP
MVP

Provide 5 lines of sample data...

Not applicable
Author

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

Not applicable
Author

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
MVP
MVP

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
Author

Thank you.

I never used a loop in my script.

I can start with this.

Not applicable
Author

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