topic Re: Planning to pull 7 days data in each loop using rest Api in App Development

Planning to pull 7 days data in each loop using rest Api

iahmad9 — Sat, 18 May 2024 07:08:05 GMT

Hi Team,

As per the business requirement, I need to pull 2 years of data from rest api but due to huge volume of data we can not pull two years data at a time. So I am planning to pull 7 days data using loop and finally concatenate data in final qvd.

Trying to use this logic but this is for daily basis. I need to pull 7 days data in each loop so that api not stuck.

Let vStartDate = '01/01/2023';

Let vEndDate = Date(today(),'MM/DD/YYYY');

TempDate:
LOAD Date(StartDate + IterNo() -1, 'MM/DD/YYYY') as TempDate
While StartDate + IterNo() -1 <= EndDate;

LOAD * Inline [
StartDate, EndDate
$(vStartDate), $(vEndDate)
];

CommaSeparatedDates:
LOAD Concat(distinct Date(Date#(TempDate,'MM/DD/YYYY'),'YYYYMMDD'),',') as DateRange
Resident TempDate

LET vDateRange = Peek('DateRange', 0); // using this variable in rest api filter.

Note:Due to data security i cannot share all the table details.

let me know in case of any other details to understand the requirements.

Regards,

Irshad Ahmad

Re: Planning to pull 7 days data in each loop using rest Api

rubenmarin — Sat, 18 May 2024 08:09:30 GMT

Hi, maybe a logic like:

Let vStartDate = Num(Date('01/01/2023')); Let vEndDate = Num(Date(today(),'MM/DD/YYYY')); // Initialize table DataTable: LOAD Null() as dumbField Autogenerate 0; // Start doing abucle to iterate forr each 7 days Do While $(vStartDate)<=$(vEndDate) // Only create dates for the StartDate and the next 6 days, or the EndDate if it's lower than StartDate+7 TempDate: LOAD Date(StartDate + IterNo() -1, 'MM/DD/YYYY') as TempDate While StartDate + IterNo() -1 <= RangeMin(EndDate, StartDate + 6); LOAD * Inline [ StartDate, EndDate $(vStartDate), $(vEndDate) ]; CommaSeparatedDates: LOAD Concat(distinct Date(Date#(TempDate,'MM/DD/YYYY'),'YYYYMMDD'),',') as DateRange Resident TempDate LET vDateRange = Peek('DateRange', 0); // using this variable in rest api filter. // After API query add data to table Concatenate (DataTable) LOAD * Resident APIResultTable; // Delete tables used in the bucle to clean for next iteration DROP Tables APIResultTable, CommaSeparatedDates, TempDate; // Add days to start date fo next iteration of do..while Let vStartDate = $(vStartDate)+7; // End do..while Loop // Drop dumbField from table DROP Field dumbField;

Re: Planning to pull 7 days data in each loop using rest Api

iahmad9 — Mon, 20 May 2024 07:39:35 GMT

Hi @rubenmarin ,

Thank you for share the logic.

I checked this it seems good but when i am executing this code script data loading continuously.

As for example:

Let vStartDate = Num(Date('01/01/2023')); //Let vEndDate = Num(Date(today(),'MM/DD/YYYY')); Let vEndDate = Num(Date('01/14/2023'));

starting of the script just i have changed end date 2 week only but script not getting finished in two loop. it is continue running i am not sure what is wrong with us. I would like to request you please cross check once. Thank you once again for your help.

Re: Planning to pull 7 days data in each loop using rest Api

rubenmarin — Mon, 20 May 2024 08:44:37 GMT

Hi, be sure to add the 7 days to vStartDate before the next iteration of the bucle, so it only repeats while StartDate is lower than EndDate.

You can execute in debug mode to check the values of each variable in each iteration

Re: Planning to pull 7 days data in each loop using rest Api

iahmad9 — Tue, 21 May 2024 06:22:21 GMT

Hi @rubenmarin ,

Thank you for responce.

I also tried to execute this code in debug mode. Getting same issue. 7 days adding in each time but looping infinite. It would be great if you test this code from your end. Please find the code whihc i am executing in debug mode.

Trace Full Load;
Let vStartDate = Num(Date('01/01/2023'));
//Let vEndDate = Num(Date(today(),'MM/DD/YYYY'));
Let vEndDate = Num(Date('01/14/2023'));
// Initialize table
DataTable:
LOAD Null() as dumbField Autogenerate 0;

// Start doing abucle to iterate forr each 7 days
Do While $(vStartDate)<=$(vEndDate)

// Only create dates for the StartDate and the next 6 days, or the EndDate if it's lower than StartDate+7
TempDate:
LOAD Date(StartDate + IterNo() -1, 'MM/DD/YYYY') as TempDate
While StartDate + IterNo() -1 <= RangeMin(EndDate, StartDate + 6);

LOAD * Inline [
StartDate, EndDate
$(vStartDate), $(vEndDate)
];

CommaSeparatedDates:
LOAD Concat(distinct Date(Date#(TempDate,'MM/DD/YYYY'),'YYYYMMDD'),',') as DateRange
Resident TempDate;

LET vDateRange = Peek('DateRange', 0); // using this variable in rest api filter.

Let vStartDate = $(vStartDate)+7;

// End do..while
Loop

// Drop dumbField from table
DROP Field dumbField;
// After API query add data to table
// Concatenate (DataTable)
// LOAD * Resident APIResultTable;
exit script;

Regards,

Irshad Ahmad

Re: Planning to pull 7 days data in each loop using rest Api

iahmad9 — Tue, 21 May 2024 14:23:25 GMT

Hi Team,

Still, I am looking for the actual logic to pull the 7 days or less than 7 days data in rest API in loop.

I am also trying from my side but not getting the expected solution. It would be much appreciated if you could provide some idea or logic.

Please refer the above conversation for more details.

Regards,

Irshad Ahmad

Re: Planning to pull 7 days data in each loop using rest Api

rubenmarin — Tue, 21 May 2024 16:30:51 GMT

Hi, sorry, I'm very busy these days... I tried and you are right: it doesn't ends.

As I suppose the inital Startdate will be always lower than enddate, I just moved the while to the 'Loop' sentence instead of doing in the 'Do': Loop While $(vStartDate)<=$(vEndDate)

Remember that you also need to do the drop table to avoid the inline and CommaSeparatedDates tables to add rows in each iteration

Re: Planning to pull 7 days data in each loop using rest Api

iahmad9 — Tue, 21 May 2024 17:42:23 GMT

Hi @rubenmarin ,

Thank you for your valuable time, just want to confirm you are saying use While $(vStartDate)<=$(vEndDate) instead of do While $(vStartDate)<=$(vEndDate)?

Regards,

Irshad Ahmad

Re: Planning to pull 7 days data in each loop using rest Api

iahmad9 — Wed, 22 May 2024 01:44:38 GMT

Thank you @rubenmarin I got the expected output. Thank you once again for you time to support.