Spliting part of data based on %value

[daniel_kusiak]

Hi Community,

Let's say that my data model looks like this:

Prim Class	Sec Class	Description	RefNo	Value	Wsk	Value*Wsk
1	1	Desc 1	111	100	100%	100
1	1	Desc 1	222	100	100%	100
1	1	Desc 1	333	100	100%	100
2	2	Desc 2	444	100	100%	100
2	2	Desc 2	555	100	100%	100
2	2	Desc 2	666	100	100%	100
1	2	Desc 2	777	100	50%	50

Please notice that last position RefNo 777 has Wsk=50%. Now, what i want to do is to return missing 50% to it's primary class, so the results look like this:

Prim Class	Sec Class	Description	RefNo	Value	Wsk	Value*Wsk
1	1	Desc 1	111	100	100%	100
1	1	Desc 1	222	100	100%	100
1	1	Desc 1	333	100	100%	100
1	1	Desc 1	777	50	100%	50
2	2	Desc 2	444	100	100%	100
2	2	Desc 2	555	100	100%	100
2	2	Desc 2	666	100	100%	100
1	2	Desc 2	777	50	100%	50

Now, please notice that position RefNo 777 now exist in Sec Class 1 and 2.

Is there any way to achive this?

Best regards

Daniel

[antoniotiman]

Hi Daniel,

what if

Sec Class 1  100%

Sec Class 2  100%

Sec Class 3    50%

Sec Class 4    25%

........

[daniel_kusiak]

Unfortunately i can't use this solution. The RefNo 777 must be splited between SecClass 1 and 2 based on %value of Wsk.

[effinty2112 · Accepted solution]

Hi Daniel,

          Try this script

Source:

LOAD [Prim Class],

     [Sec Class],

     Description,

     RefNo,

     Value,

     Wsk,

     [Value*Wsk]

FROM

[https://community.qlik.com/thread/266087]

(html, codepage is 1252, embedded labels, table is @1);

NoConcatenate

OK:

Load * Resident Source Where Wsk  = 1;

NoConcatenate

NotSoGood:

Load * Resident Source Where Wsk  <> 1;

NoConcatenate

ToBeAppendedToOK:

Load

[Prim Class],

     [Sec Class],

     Description,

     RefNo,

     Value * Wsk as Value,

     1 as Wsk,

      [Value*Wsk]

Resident NotSoGood;

Load

[Prim Class],

    1 as [Sec Class],

     'Desc 1'  as Description,

     RefNo,

     Value * (1-Wsk) as Value,

     1 as Wsk,

      [Value*Wsk]

Resident NotSoGood;

Drop Tables Source, NotSoGood;

Concatenate(OK)

LOAD * Resident ToBeAppendedToOK;

DROP Table ToBeAppendedToOK;

Cheers

Andrew

[daniel_kusiak]

Nice one 🙂 If we ever meet in person i'll buy you a beer.

Thank you.

[effinty2112]

Hi Daniel,

I'll hold you to that!!!

Andrew