topic Need help in getting loop on loop (For loop) in QlikView

Need help in getting loop on loop (For loop)

vkolasani — Sat, 16 Nov 2024 20:14:16 GMT

Hi Guys,

I am looking for a help in getting the solution for following scenario.

1. I am trying to get the data from REST API and need to pass the state and from_date and to_date parameters to filter the data.

2. I would like to control the parameters using the inputs in the attached excel sheet which has State_Param, Date_Param tabs.

3. Each state in the State_Param has to execute all the from_date and to_date parameters independently.

Example: State "MI" has to complete the loop of 14 date combinations presented in Date_Param tab in the excel and then loop has to begin with another state and so on.

17 states and 14 dates combinations: Each state 14 dates and reset the loop with another state in the list. 17 * 14 times.

I am passing these parameters to REST API. I have verified the State looping and looking for solution for both State and date parameters.

Sample URL: CUSTOM CONNECT TO "Provider=QvRestConnector.exe;url=https://api.xyz.com/sellers/search/v3?country%2US&state%2$(vState)&date%2$(vFrom_Date)TO$(vTo_Date)

Thanks in Advance!!

Re: Need help in getting loop on loop (For loop)

rubenmarin — Wed, 21 Aug 2019 10:21:40 GMT

Hi, I think it can be done with something similar to this:

// Load states States: LOAD State FROM [.\Qlikview_loop_states_and_dates.xlsx] (ooxml, embedded labels, table is State_Param); //Generate variables FOR i=1 to NoOfRows('States') LET vState_$(i) = Peek('State', $(i)-1, 'States'); NEXT // Store row number in variable and delete table LET vNumStates = NoOfRows('States'); DROP Table States; // Load dates Dates: LOAD fromt_date, to_date FROM [.\Qlikview_loop_states_and_dates.xlsx] (ooxml, embedded labels, table is Data_Param); //Generate variables FOR i=1 to NoOfRows('Dates') LET vFrom_Date_$(i) = Peek('fromt_date', $(i)-1, 'Dates'); LET vTo_Date_$(i) = Peek('to_date', $(i)-1, 'Dates'); NEXT // Store row number in variable and delete table LET vNumDates = NoOfRows('Dates'); DROP Table Dates; //Use variables in bucles FOR vNumState=1 to $(vNumStates) FOR vNumDate=1 to $(vNumDates) CUSTOM CONNECT TO "Provider=QvRestConnector.exe;url=<a href="https://api.xyz.com/sellers/search/v3?country%2US&state%2$(vState_$(vNumState))&date%2$(vFrom_Date_$(vNumDate))TO$(vTo_Date_$(vNumDate" target="_blank">https://api.xyz.com/sellers/search/v3?country%2US&state%2$(vState_$(vNumState))&date%2$(vFrom_Date_$(vNumDate))TO$(vTo_Date_$(vNumDate</a>))"; // Do something NEXT NEXT

Re: Need help in getting loop on loop (For loop)

rubenmarin — Wed, 21 Aug 2019 10:23:44 GMT

Custom connect looks messy, it should be:

CUSTOM CONNECT TO "Provider=QvRestConnector.exe;url=https://api.xyz.com/sellers/search/v3?country%2US&state%2$(vState_$(vNumState))&date%2$(vFrom_Date_$(vNumDate))TO$(vTo_Date_$(vNumDate))";