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

[iahmad9]

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

[rubenmarin · Accepted solution]

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;

[iahmad9]

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.

[rubenmarin]

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

[iahmad9]

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

[iahmad9]

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

[rubenmarin · Accepted solution]

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

[iahmad9]

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 

[iahmad9]

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