I seem to have a problem that is very difficult to solve using Qliksense because the most basic SQL statements don't work. I could do this on an SQL Server where I can use nested statements, but even after figuring out how to use mapping and updating tables by reloading them into a new table, the following problem doesn't seem to be easily solveable in QlikSense.
I have a base table that looks like this:
OrderNumber OrderPosition TotalAmountDelivered MaxAmountInPosition StillToDeliver Delivered
1234567 1 400 50 0 50
1234567 2 400 30 0 30
1234567 3 400 420 100 320
8987654 1 300 500 200 300
The field StillToDeliver and Delivered is what I have to calculate. The amount TotalAmountDelivered is only known per OrderNumber, not per OrderPosition inside the OrderNumber. An OrderNumber can be split into several rows having a different OrderPosition. Each OrderPosition can only hold a MaxAmountInPosition. The TotalAmountDelivered needs to be split above all OrderPositions, by filling up the first position as much as it can have (MaxAmountInPosition) and then the second position etc. until the whole TotalAmountDelivered is gone. I would do that in a loop from my Min(OrderPosition ) to my Max(OrderPosition) and I would do the following:
LET MinOrderPosition = MIN(OrderPosition );
LET MaxOrderPosition = MAX(OrderPosition );
For CurrentOrderPosition= MinOrderPosition to MaxOrderPosition
- Calculate the SUM(StillToDeliver) after grouping the table to OrderNumber to know what value has already been assigned for a certain OrderNumber over all rows, then substract this result from TotalAmountDelivered to know what amount still has to be delivered (let's call this result StillToAssign)
- Calcutate what amount can be assigned to the CurrentOrderPosition by getting the max value of StillToAssign and MaxAmountInPosition in the same row, then set this value to the field Delivered and Calculate the field StillToDeliver by substracting Delivered from MaxAmountInPosition
- Proceed with the next CurrentOrderPosition
Next CurrentOrderPosition
Any help would be greatly appreciated to code the missing pieces that I described. I wrote quite some code in QlikSense already but I still have trouble understanding how Qliksense knows which fields from which table it loads when I need to work with preceeding loads to get a group by, so for me as an SQL programmer, programming in QlikSense is just very unsettling even if the result is correct. And in this example, I feel helpless working with the limited QlikSense commands.
