Split values based on dimension on dynamically.

[agni_gold]

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.

[Anonymous]

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

[agni_gold]

Thanks Kapa ,

But this requirement is on script level.

[#4]

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.

[vinieme12]

Hi,

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

[sunny_talwar]

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

[vinieme12]

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;