Flaged row not being stored in QVD/Left join

Nolgath — Fri, 27 Sep 2024 07:09:08 GMT

Hi,

I have this app that does the following: (It's supose to do but is not working properly) :

Load multiple QVD's and concatenate them.

This table created from those QVD's will have fields "key" and "request_status" , request_status can have "Pending","Answered" values.

I then do a loop and check if the request_status is "Answered" in a given row, after that it will store into another new field named AnsweredFlag and give it a value "Answered" so it basicly becomes flagged after the loop.

The flagged keys will then be stored into a qvd and left joined to the original table where all the qvd's are.

My send_email.qvd then won't have the flagged keys and this loop will always send the emails... any clues?

But the issue is that, it stores the values into the table but when I reload the app it seems to not have appended anything to the flags. Please see script:

My table:

Requests_triggers:

Load * FROM [lib://FORMS_DATA (cars2clickvm_radu.chitu)/Deal Monitor Outputs/Deal_Monitor_*_FINAL.qvd] (qvd);

left join(Requests_triggers)
LOAD
key,
vAnsweredFlag
FROM [lib://FORMS_DATA (cars2clickvm_radu.chitu)/sent_emails.qvd] (qvd);

LIB CONNECT TO 'SMTP - BREVO (cars2clickvm_radu.chitu)';

// Create a temporary table to collect answered keys
TempAnsweredKeys:
LOAD
'' as TempKey,
'' as TempAnsweredFlag
AUTOGENERATE 0;

//THIS IGNORES THE ONES THAT DONT HAVE THE VANSWEREDflag BECAUSE IT NEVER LOOPS THROUGHT IT

// Loop through the Requests_triggers
For i = 0 to NoOfRows('Requests_triggers') - 1
LET vRecipientName = Peek('KAM', $(i), 'Requests_triggers');
LET vListname = Peek('listname', $(i), 'Requests_triggers');
LET vAnsweredFlag = Peek('request_status', $(i), 'Requests_triggers'); //request status check
LET vFeedback = Peek('reply', $(i), 'Requests_triggers');
LET vkey = Peek('key', $(i), 'Requests_triggers');
LET vkey_check = Peek('vAnsweredFlag', $(i), 'Requests_triggers');

Trace ---------- $(vRecipientName) , $(vAnsweredFlag) , $(vkey_check) , $(vListname);

//There is always the last keys that's why it doesnt work, i need to find a way to only check the keys added by the loop.
IF vAnsweredFlag = 'Answered' and (isnull(vkey_check) or vkey_check<>'Answered') then // Check if vkey exists in Sent
// and vkey_check <> 'Answered'
// Construct HTML message with personalized content
LET vMessageHTML = '
<html>
<body style="font-family: Arial, sans-serif; color: #333333; line-height: 1.5;">
<div style="max-width: 600px; margin: auto; border: 1px solid #dddddd; padding: 20px; border-radius: 8px; background-color: #f9f9f9;">
<h2>Hello ' & vRecipientName & '</h2>
<h2>List Name : ' & vListname & '</h2>
<h2>Feedback : ' & vFeedback & '</h2>
</div>
</body>
</html>';

TRACE ------------------------------- Loop instance $(i) -------------------------------;

// Add the key to the temporary table
CONCATENATE (TempAnsweredKeys)
LOAD
'$(vkey)' as TempKey,
'Answered' as TempAnsweredFlag
AUTOGENERATE 1;

// Send email using the specified properties
SENDMAIL:
LOAD
'Sent' as [SendEmail.status],
'Success' as [SendEmail.result],
'$(vRecipientEmail)' as [RecipientEmail]
INLINE [
status, result
Sent, Success
];

// Load and execute the send email action
SELECT status,
result
FROM SendEmail
WITH PROPERTIES (
to='czo@cars2click.com',
subject='Deal Monitor - You have Feedback',
message='$(vMessageHTML)',
html='true',
fromName='Demo - CZO',
fromEmail='maria@cars2click.com'
);

// Finalize the temporary table by concatenating with Sent
ENDIF;
Next;

store TempAnsweredKeys into [lib://FORMS_DATA (cars2clickvm_radu.chitu)/sent_emails_test.qvd] (qvd);

// Now perform a LEFT JOIN outside the loop to update the Requests_triggers table
LEFT JOIN (Requests_triggers)
LOAD
trim(TempKey) as key,
TempAnsweredFlag as vAnsweredFlag
RESIDENT TempAnsweredKeys;

// Drop the temporary table as it's no longer needed
DROP TABLE TempAnsweredKeys;

// Store the final result
STORE Requests_triggers into [lib://FORMS_DATA (cars2clickvm_radu.chitu)/sent_emails.qvd] (qvd);

Re: Flaged row not being stored in QVD/Left join

rubenmarin — Sat, 28 Sep 2024 07:16:24 GMT

Hi, if you do the join using the same vAnsweredFlag field, but the value has changed (like from null to something), the join wont work as all the fields used for join should have the same values.

You can avoid this renaming the first vAnsweredFlag to something like vAnsweredFlag_prev, so using a different field it will do the join of the changed values, but also the loop you shlud also fill the value of this field for the unprocesssed rows.

Another option coud be: Apply the 'IF' conditions in a temporary table before the loop, then you can loop all the rows of this table without the need of any checks, and the end concatenate the modified rows with the existing and unprocessed rows in the qvd to recreate the qvd with all the keys. You can use Exist() to do the check and only add the unprocessed rows from the qvd.

I would reccomend to have a copy of the qvds while developing this, so in case anything doesn't work as expected you can returns to the original state of the flags.

topic Re: Flaged row not being stored in QVD/Left join in App Development

Flaged row not being stored in QVD/Left join

Re: Flaged row not being stored in QVD/Left join