Not Joining Values

[Nolgath]

Hi,

I have a script that has a loop that checks for a value in a field. When it finds it it should add a flag to it in Field called flag with number 1, but it doesnt do it, i am not sure why.

My Script:

Requests_triggers:

LOAD * FROM [lib://FORMS_DATA (cars2clickvm_radu.chitu)/Deal Monitor Outputs/Deal_Monitor_BM_FINAL.qvd] (qvd); //Brian Madsen

left join (Requests_triggers)

LOAD

key, 

Flag

FROM [lib://FORMS_DATA (cars2clickvm_radu.chitu)/Request_Flags.qvd] (qvd);

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

TempTable_Request_Status:

Load 

'' as key_temp,

'' as Flag_temp

AutoGenerate 1;

// Loop through the Requests_triggers

For i = 0 to NoOfRows('Requests_triggers') - 1

    LET vRequest_Status = Peek('request_status', $(i), 'Requests_triggers');

    LET vkey = Peek('key', $(i), 'Requests_triggers');

IF vRequest_Status = 'Answered' THEN

        Concatenate(TempTable_Request_Status)

        Load 

        '$(vkey)' as key,

        1 as Flag 

        AutoGenerate 1;

        TRACE -----------------------;

        TRACE -- vkey: $(vkey) --;

        TRACE -----------------------;

    END IF;

NEXT;

Left Join (Requests_triggers)

Load 

key_temp as key,

Flag_temp as Flag

Resident TempTable_Request_Status;

// Drop TempTable

DROP TABLE TempTable_Request_Status;

Store Requests_triggers into [lib://FORMS_DATA (cars2clickvm_radu.chitu)/Request_Flags.qvd] (qvd);

[p_verkooijen]

The join does not produce records because your loop contains other fields than the join 

TempTable_Request_Status:

Load 

'' as key_temp,

'' as Flag_temp

AutoGenerate 1;

// Loop through the Requests_triggers

For i = 0 to NoOfRows('Requests_triggers') - 1

    LET vRequest_Status = Peek('request_status', $(i), 'Requests_triggers');

    LET vkey = Peek('key', $(i), 'Requests_triggers');

IF vRequest_Status = 'Answered' THEN

        Concatenate(TempTable_Request_Status)

        Load 

        '$(vkey)' as key,

        1 as Flag 

        AutoGenerate 1;

        TRACE -----------------------;

        TRACE -- vkey: $(vkey) --;

        TRACE -----------------------;

    END IF;

NEXT;

Left Join (Requests_triggers)

Load 

key_temp as key,

Flag_temp as Flag

Resident TempTable_Request_Status;

// Drop TempTable

DROP TABLE TempTable_Request_Status;

Change the field names in the For loop to key_temp and Flag_temp.

Also no ; needed for END IF and NEXT

IF vRequest_Status = 'Answered' THEN

        Concatenate(TempTable_Request_Status)

        Load 

        '$(vkey)' as key_temp ,

        1 as Flag_temp

        AutoGenerate 1;

        TRACE -----------------------;

        TRACE -- vkey: $(vkey) --;

        TRACE -----------------------;

    END IF

NEXT

[Kushal_Chawda]

@Nolgath  You are connecting to data connection below but you are not using that anywhere in your loop. Then what is the use of calling that connection string?

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

[Nolgath]

The LIB CONNECT TO 'SMTP - BREVO (cars2clickvm_radu.chitu)'; is a connection to a REST API, for the email sending.

I use in the loop my table "Requests_triggers"

The goal is to run the loop through the table Requests_triggers finding the value "Answered" inside the field "request_status"

[Kushal_Chawda]

@Nolgath  It seems you are complicating it through for loop which is not needed that's what I think. You can create the Flag just with if condition in your data.

Requests_triggers:
LOAD *,
     if(request_status='Answered',1) as Flag
FROM [lib://FORMS_DATA (cars2clickvm_radu.chitu)/Deal Monitor Outputs/Deal_Monitor_BM_FINAL.qvd] (qvd); 

Store Requests_triggers into [lib://FORMS_DATA (cars2clickvm_radu.chitu)/Request_Flags.qvd] (qvd);

[Nolgath]

That will not work because when it goes through the loop to send emails per each row, it will ignore those from the start, they need to pass throught the loop, send the emails, flag them so on the next reload of the app those emails already sent will not be sent again.

[p_verkooijen]

@Nolgath did you check my previous comment? 

Change the field names in the For loop to key_temp and Flag_temp 

IF vRequest_Status = 'Answered' THEN

        Concatenate(TempTable_Request_Status)

        Load 

        '$(vkey)' as key_temp ,

        1 as Flag_temp

        AutoGenerate 1;

        TRACE -----------------------;

        TRACE -- vkey: $(vkey) --;

        TRACE -----------------------;

    END IF

NEXT

[Kushal_Chawda]

@Nolgath  try to change your code as below

TempTable_Request_Status:
Load 0 as Junk
AutoGenerate 0;

// Loop through the Requests_triggers
For i = 0 to NoOfRows('Requests_triggers') - 1
LET vRequest_Status = Peek('request_status', $(i), 'Requests_triggers');
LET vkey = Peek('key', $(i), 'Requests_triggers');

IF '$(vRequest_Status)' = 'Answered' THEN
Concatenate(TempTable_Request_Status)
Load
'$(vkey)' as key,
1 as Flag
AutoGenerate 1;

TRACE -----------------------;
TRACE -- vkey: $(vkey) --;
TRACE -----------------------;
END IF;
NEXT;

Left Join (Requests_triggers)
Load key,
         Flag
Resident TempTable_Request_Status;

// Drop TempTable
DROP TABLE TempTable_Request_Status;