Need help in getting loop on loop (For loop)

[vkolasani]

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!!

[rubenmarin]

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

[rubenmarin]

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))";