If position 1 = Device, then next position, etc.

[reporting_neu]

Hello, 

I have the following values ​​in a table:

I need to assign the batteries to the devices.

To do this, I need to check whether, for example, position X is a device. If so, then please take the next position with battery. Then check again whether the next position is also a device; if so, then take the battery from the next position... and so forth per order.

This should be checked consistently.

Result:

Can anyone of you help me and tell me how to do this in the loading script?

[diegozecchini]

Hi!
I undesrtood you have a data structure like this

order pos part name quantity
500001 10,0 10209401 Device 1
500001 20,0 1015083 Battery 18
500001 30,0 10209401 Device 1
500001 30,1 1015083 Battery 18
500001 40,0 10209401 Device 3
500001 50,0 1015083 Battery 54
you can achieve with Qlik using peek and previous

// Step 1: Load and sort raw data
RawSorted:
LOAD
order,
num(replace(pos, ',', '.')) as PosNum, // To sort correctly
pos,
part,
name,
quantity
RESIDENT RawData
ORDER BY order, PosNum;

// Step 2: Create final table pairing devices and batteries
DeviceBatteryMap:
LOAD
order,
pos as device_pos,
part as device_part,
name as device_name,
quantity as device_quantity,
Peek('part') as battery_part,
Peek('name') as battery_name,
Peek('quantity') as battery_quantity
RESIDENT RawSorted
WHERE name = 'Device';

does it work for you?

[reporting_neu]

Thank you for your answer.
Unfortunately, the peek-fields only return null.