Join in memory data to resident table

[bmenicucci]

Hello experts!

I'm struggling with creation of a table that should contain some "Alerts" according to few specific rules.
Can someone assist me in identifying what's wrong in my script? I've tried multiple ways to add the Alerts to table but never succeeded. 😞

TempAlert:
LOAD
KeyAlert,
ShowTag,
TagAlert,
MaxValue,
MinValue,
SetValue,
GreenLight,
AmberLight,
RedLight,
ActualNum,
SetValueNum,
SetMinValueNum,
SetMaxValueNum,
MinValueNum,
MaxValueNum
FROM [C:\QlikResource\1.Example\1.Dev\2.QVD\1.Extract\TempAlert.qvd] (qvd);

FOR FieldCntr = 1 to FieldValueCount('KeyAlert');

SET DecimalSep='.';
LET vKeyAlert = FieldValue('KeyAlert',$(FieldCntr));
LET vActualNum = num(lookup('ActualNum','KeyAlert','$(vKeyAlert)'));
LET vSetValueNum = num(lookup('SetValueNum','KeyAlert','$(vKeyAlert)'));
LET vSetMinValueNum = num(lookup('SetValueNum','KeyAlert','$(vKeyAlert)')*0.98);
LET vSetMaxValueNum = num(lookup('SetValueNum','KeyAlert','$(vKeyAlert)')*1.02);
LET vMaxValueNum = num(lookup('MaxValueNum','KeyAlert','$(vKeyAlert)'));
LET vMinValueNum = num(lookup('MinValueNum','KeyAlert','$(vKeyAlert)'));
LET vL.Greenlight = lookup('GreenLight','KeyAlert','$(vKeyAlert)');
LET vL.Amberlight = Lookup('AmberLight','KeyAlert','$(vKeyAlert)');
LET vL.Redlight = Lookup('RedLight','KeyAlert','$(vKeyAlert)');

IF $(vL.Greenlight) THEN
LET vL.Alert = 'G';
ELSEIF $(vL.Amberlight) THEN
LET vL.Alert = 'A';
ELSEIF $(vL.Redlight) THEN
LET vL.Alert = 'R';
ELSE
LET vL.Alert = 'N/A';
ENDIF

LEFT JOIN (TempAlert)
LOAD
'$(vKeyAlert)' AS KeyAlert,
'$(vL.Alert)' AS Alert
RESIDENT TempAlert;
;

LET vL.Greenlight = ;
LET vL.Amberlight = ;
LET vL.Redlight = ;
LET vKeyAlert=;
LET vL.Alert=;

NEXT;

Please find attached the mentioned QVD.
Thanks in advance for your support!
Brunello

[rubenmarin · Accepted solution]

Hii, check if the first left join works, if not its' because the IF..THEN..ELSEIF needs some ajustement, you can check the reload log to see how the $() expands.

After that you will have an issue and it is than this kind of left join can't work inside a FOR..NEXT bucle becuase after the first iteration the Alert field already exists in the TempAlert table, so it will try to join rows where two fields has the same value, not only by KeyAlert as you expect.

Try creating a temporary table where you add the different alerts and after the FOR..NEXT bucle do the Left Join

tmpAlertByKey: NoConcatenate LOAD [dumbField];
FOR...
  IF...ELSEIF...ELSE..ENDIF
  Concatenate(tmpAlert) 
  LOAD ...KeyAlert...Alert...
NEXT

Left Join (TempAlert)
LOAD KeyAlert, Alert Resident tmpAlertByKey;

DROP Table tmpAlertByKey;

[bmenicucci]

Hi @rubenmarin, 
your suggestion actually led me in the right direction and I've been able to get it properly! Thank you so much!! 🤗
Brunello