Reproducing a specific behavior from an old DB

[LesJean]

Hello everyone,

I'm tasked with transferring an old system which used an SQL database onto the Qlik Sense platform. Since we do not have the original SQL script anymore, I'm reproducing the SQL code based on the documentation we have, here's the excerpt of the behavior I'm trying to reproduce.

If tblArticleBO/bo_adv = "BO STOCK" for a given article, validate the total qty of BO for the commandes with tblCommande/limit_date < Today.
    - If total qty of BO > 0, update tblRuptureToday/bo_reason = "STOCK"
    - If total qty of BO <= 0, do not include in tblRuptureToday

I'm having trouble reproducing this behavior in code, here's what I came up with so far:

tblCommande:
LOAD
  article_id
  limit_date
  bo_qty
FROM x

tblArticleBO:
LOAD
  article_id
  bo_id
  bo_adv
FROM x

NoConcatenate

tblRuptureToday:
LOAD
  bo_id
  IF("bo_adv"='BO STOCK', 
    IF(limit_date < today(), 
      IF(/*Sum of all bo_qty for this specific article_id*/ > 0, 'STOCK', /*do not include in tblRuptureToday*/
    ))) as bo_reason
RESIDENT tblArticleBO

I can't seem to wrap my head around the exact syntax I need to code this behavior. The data structure looks like this is this is any help:

Any help would be greatly appreciated.

Thanks,

LesJean

[Channa]

Sum of all bo_qty for this specific article_id

aggr(sum(bo_qty),artical_id) 

it means that expression

sum of quantity for each artical id

[rogerpegler]

Assuming tblCommande has many records per article per date, create a temporary summary table:

tblCommandeSummary:

Load
article_id,
limit_date,
sum(bo_qty) as daily_bo_qty
Resdient tblCommande
Group by article_id,limit_date;

You can then join this table to tblArticleBO and use the resultant table as the source for tblRuptureToday, in particular using daily_bo_qty to compare to zero in your if statement.