Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Nov. 20th, Qlik Insider - Lakehouses: Driving the Future of Data & AI - PICK A SESSION
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Splitting data of a field based on count of another field

Hi All,

I have data as below

table:

load * inline [

Donator_ID,Donator_Time,Donation1,Donation2,Donation3,Donation4,Donation5,Donation6,Acceptor_ID,Acceptor_Time,Acceptor_1,Acceptor_2,Acceptor_3,Acceptor_4,Acceptor_5,Acceptor_6

1,      12,    13,       62,      15,    88,     6,       5,         6,    12,          211,     - ,          38,       -,    144,        -  

1,      12,    13,       62,      15,    88,     6,       5,   7,    12,           -,       83,         - ,          -,    31,         -  

];

If my Donator_Time  is equal to  Acceptor_Time  then

step 1

i need to calculate the no of  Acceptor_ID

in the above data for my Donator_Time  for donator_ID 1 is 12

so for Donator_Time 12 i have two Acceptor_IDs  6 and 7

i can have any no of records

step2:

now i need is i want to  the calculation  Acceptor_1-(Donation1/2)   (Acceptor_1 minus(Donation1 divided by 2)

this will give me the below out put  for  acceptor

   

Acceptor_ID,Acceptor_Time,Acceptor_1_NewAcceptor_2_NewAcceptor_3_NewAcceptor_4_NewAcceptor_5_NewAcceptor_6_New
612           205                -                 30                -              141                -  
712               -                 52                -                  -                 28                -  
2 Replies
vishsaggi
Champion III
Champion III

Try this?

table:

load *, IF(Acceptor_Time = Donator_Time, 1,0) AS Flag inline [

Donator_ID,Donator_Time,Donation1,Donation2,Donation3,Donation4,Donation5,Donation6,Acceptor_ID,Acceptor_Time,Acceptor_1,Acceptor_2,Acceptor_3,Acceptor_4,Acceptor_5,Acceptor_6

1,      12,    13,       62,      15,    88,     6,       5,         6,    12,          211,     - ,          38,       -,    144,        - 

1,      12,    13,       62,      15,    88,     6,       5,   7,    12,           -,       83,         - ,          -,    31,         - 

];

Using straight table:

Dim

1. = IF(Flag = 1, Acceptor_ID)

2. = IF(Flag = 1, Acceptor_Time)

Expr:

= Acceptor_1 - (Donation1/2) ....... create 6 expressions till

.....

= Acceptor_6 - (Donation6/2)

marcus_sommer

Maybe a transforming of the data-structure per The Crosstable Load would be useful.

- Marcus