Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!
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
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))";