Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hey,
I have issues with this structure of data. I need split values Using Qlik sense script.
Logic behind data is:
I have Number of pallets (places within truck). But it has summarized value only (FINV_Real_Value) only in rows with number of pallets. All rows below with 0 belong to the same value.
So, my calculation should be I pick total value for 9 pallets - 1411 EUR (second row) and i need divide this with number of rows (2) which are belong to same FINV_Real_Value (in 4th row start new position). And split this value into each record proportionally. Expected result is visible in column "NEED THIS"/
Ok, so there is no unique key per rows to be grouped, right?
If this is the case, you can add those 3 fields in your loading script:
If(Number_Of_Pallets=0,Peek(Previous_Number_Of_Pallets),Number_Of_Pallets) as Previous_Number_Of_Pallets,
If(FINV_Real_Value=0,Peek(Previous_FINV_Real_Value),FINV_Real_Value) as Previous_FINV_Real_Value,
If(Number_Of_Pallets=0,Peek(Previous_FINV_Real_Value)/Peek(Previous_Number_Of_Pallets),FINV_Real_Value/Number_Of_Pallets) as Value_Per_Pallet
If you don't have a unique key to identify which rows are together, this is a bit tricky.
Are you sorting those data or are they coming from your source already sorted?
Have you access to the loading script?
Hey, unique key with order should be " Loading_Detail_ID ", My source data are sorted according to this col.
Sample data are available within the uploaded app.
Ok, so there is no unique key per rows to be grouped, right?
If this is the case, you can add those 3 fields in your loading script:
If(Number_Of_Pallets=0,Peek(Previous_Number_Of_Pallets),Number_Of_Pallets) as Previous_Number_Of_Pallets,
If(FINV_Real_Value=0,Peek(Previous_FINV_Real_Value),FINV_Real_Value) as Previous_FINV_Real_Value,
If(Number_Of_Pallets=0,Peek(Previous_FINV_Real_Value)/Peek(Previous_Number_Of_Pallets),FINV_Real_Value/Number_Of_Pallets) as Value_Per_Pallet