topic Re: Split values based on dimension on dynamically. in QlikView

Split values based on dimension on dynamically.

agni_gold — Tue, 26 Jul 2016 07:47:54 GMT

I have a situation here , i have multiple dimension and want to split the values of one value to other two values ,

attaching my qvw file here i am spiting A 's 41% to B and 59%to C , it is working fine , but when we have lot of other dimensions , then the split is not correct , please suggest.

Re: Split values based on dimension on dynamically.

Anonymous — Tue, 26 Jul 2016 09:41:22 GMT

Hi Agnivesh,

I am not sure if I have understood the question or not. But according to my understanding you can covert that straight table in to pivot and check the partial sum checkbox from the Presentation tab.

Please find the screenshot

Or else You can go with the below screenshot as well, that would give you a better picture of the sums you are doing

Regards

Your Buddy

Re: Split values based on dimension on dynamically.

agni_gold — Tue, 26 Jul 2016 09:55:43 GMT

Thanks Kapa ,

But this requirement is on script level.

Re: Split values based on dimension on dynamically.

Tue, 26 Jul 2016 10:20:14 GMT

hi,

try this .

DIVIDE:

LOAD ID,

Amt

FROM

DIVIDE.xlsx

(ooxml, embedded labels, table is Sheet1);

Left join(DIVIDE)

LOAD ID,SUM(Amt) as total_amt Resident DIVIDE Group by ID;

Dividefinal:

LOAD *, ((41*total_amt)/100) as has41,((59*total_amt)/100) as has59 Resident DIVIDE;

DROP Table DIVIDE;

Thanks.

Re: Split values based on dimension on dynamically.

vinieme12 — Tue, 26 Jul 2016 10:23:30 GMT

Hi,

Can you share sample data and explain/show desired output?

Re: Split values based on dimension on dynamically.

sunny_talwar — Tue, 26 Jul 2016 10:50:32 GMT

I agree with vinieme12‌, not entirely sure what you are trying to do. Can you elaborate a more on your requirement?

Re: Split values based on dimension on dynamically.

vinieme12 — Tue, 26 Jul 2016 11:59:14 GMT

Not completely sure what you are trying to do here, but this should give you some insight

FACT:

LOAD * INLINE [

ID,Amt

A,10

A,32

A,45

B,12

B,20

B,22

B,32

C,23

C,30

C,34

C,43

C,54

];

left Join

MapSubSplit:

LOAD * INLINE [

ID,Split49ID,Split51ID

A,B,

A,,C

];

left Join(FACT)

SplitTable:

LOAD

Split49ID as ID,

sum(Amt)*0.49 as [49HS_Amt]

RESIDENT FACT

Group By Split49ID;

left Join(FACT)

SplitTable2:

LOAD

Split51ID as ID,

sum(Amt)*0.51 as [51HS_Amt]

RESIDENT FACT

Group By Split51ID;