topic Re: For loop id should be as null or zero in App Development

For loop id should be as null or zero

kavita25 — Wed, 07 Feb 2024 06:04:36 GMT

Hello Everyone,

Load * Inline [

];

M passing these ids in another table through for loop, but if these ids does not exist in another table it throws an error, id does not exist.

I want to continue the for loop and if the id does not exist it should save as null or zero.

Please help me with this logic.

@Anil_Babu_Samineni

Thanks,

Kavita

Re: For loop id should be as null or zero

PrashantSangle — Wed, 07 Feb 2024 06:07:53 GMT

if you want continue reload with error then try below logic

before loop start ------------>>

set errormode=0;

------------------------->>>>

after loop end then again

set errormode = 1;

Regards,

Prashant Sangle

Re: For loop id should be as null or zero

Vegar — Wed, 07 Feb 2024 06:35:09 GMT

You could do a check before performing your load if the data expect to exist exsists, and I'd not then skip your load statement.

IF <logical test> THEN

ELSE

Trace WARNING: $(vValue) was skipped in loop;

ENDIF

Re: For loop id should be as null or zero

Anil_Babu_Samineni — Wed, 07 Feb 2024 07:17:48 GMT

@kavita25 I suggest the same way what @PrashantSangle shared, But I would recommend inside the loop rather before and after to avoid the genuine errors.

But, I am curious why you are comparing with ID that is not available in second table?

Re: For loop id should be as null or zero

kavita25 — Thu, 08 Feb 2024 13:19:58 GMT

Hello @Anil_Babu_Samineni I tried that but I am also storing the data in qvd. But in the qvd the data is stored only the data before the error.

I am doing this to get Worklog data from JIRA connector.

Also, I am doing this, because I want Ids from Issues table and get those ids only from the Worklog table.

Please suggest me.

Thanks,

Kavita

Re: For loop id should be as null or zero

kavita25 — Thu, 08 Feb 2024 13:23:41 GMT

@PrashantSangle I tried the code. Yes its skipping those errors. But its not saving the whole data except for those issues which does not exist. It saves only the data before error occurs.

Thank You for the suggestion.

Re: For loop id should be as null or zero

PrashantSangle — Fri, 09 Feb 2024 04:41:33 GMT

Hi,

try with below logic.

Test1:

Load * Inline [

Region

];

NoConcatenate

Test2:

Load * Inline [

Region

];

NoConcatenate

Test3:

Load * Inline [

Region

];

NoConcatenate

Final:

Load * Inline [

Region

];

Set errormode=0;

for each a in 1,7,2,4,3,5

Concatenate(Final)

LOAD * Resident Test$(a);

next;

Set errormode=1;

Drop table Test1,Test2,Test3;

Exit Script;

Regards,

Prashant Sangle

Re: For loop id should be as null or zero

rajuamet — Fri, 09 Feb 2024 12:49:13 GMT

@kavita25 are you expecting something like this?

InlineTable:
LOAD * Inline [
Id
1
2
3
4
];

OtherTable:
LOAD * Inline [
Id, DIM
1, A
2, B
4, C
];

// Create a mapping table for IDs present in OtherTable
Map_IdExists:
MAPPING LOAD
Id,
Id as Exists
RESIDENT OtherTable;

// Initialize a temporary table to store results
NoConcatenate
TempResultTable:
LOAD * INLINE [
Id, DIM
];

FOR i = 0 TO NoOfRows('InlineTable') - 1
LET vId = Peek('Id', i, 'InlineTable');
LET vExists = ApplyMap('Map_IdExists', vId, 'NotFound');

IF vExists = 'NotFound' THEN
// If ID does not exist in OtherTable, add it with NULL or 0 for DIM
CONCATENATE (TempResultTable)
LOAD
NULL() AS Id, // Replace NULL() with 0 if you prefer 0 over NULL
NULL() AS DIM // Replace NULL() with 0 if you prefer 0 over NULL
AUTOGENERATE 1
;
ELSE
// If ID exists, fetch and add its corresponding row from OtherTable
CONCATENATE (TempResultTable)
LOAD
Id,
DIM
RESIDENT OtherTable
WHERE Id = $(vId)
;
ENDIF
NEXT

// Load the final results into ResultTable
NoConcatenate
ResultTable:
LOAD DISTINCT
Id,
DIM
RESIDENT TempResultTable;

// Cleanup
DROP TABLES TempResultTable, InlineTable, OtherTable;