topic Re: Spliting part of data based on %value in QlikView

Spliting part of data based on %value

daniel_kusiak — Fri, 30 Jun 2017 12:01:53 GMT

Hi Community,

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

Prim Class	Sec Class	Description	RefNo	Value	Wsk	*ValueWsk**
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	*ValueWsk**
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

Re: Spliting part of data based on %value

antoniotiman — Fri, 30 Jun 2017 12:08:40 GMT

Hi Daniel,

what if

Sec Class 1 100%

Sec Class 2 100%

Sec Class 3 50%

Sec Class 4 25%

........

Re: Spliting part of data based on %value

daniel_kusiak — Fri, 30 Jun 2017 12:27:31 GMT

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

Re: Spliting part of data based on %value

effinty2112 — Fri, 30 Jun 2017 13:01:29 GMT

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

Re: Spliting part of data based on %value

daniel_kusiak — Fri, 30 Jun 2017 13:13:25 GMT

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

Thank you.

Re: Spliting part of data based on %value

effinty2112 — Fri, 30 Jun 2017 13:15:38 GMT

Hi Daniel,

I'll hold you to that!!!

Andrew