Loop and load from excel

[Anonymous]

Hi friends,

I have an Excel file containing many rows. Every row has 2 columns. It looks like this:

SurveyID | QuestionID

123          365

256          8456

I need to go through every line in that file and assign those two values to two variables in the qlikview load statement. Then I have an sql query that has to be executed for every line in the Excel file.

set avgNumDays = 7;

let beginDate = date(today()-1);

let endDate = date(today());

let avgBeginDate = date(today()-7);

set surveyID = from the Excel file;

set defaultLangID = 1;

set questionID = from the Excel file;

SQL

(

select   '3' AS 'orderID',

      $(beginDate) AS 'Start Date',

      $(endDate) AS 'End Date',

      A.surveyID AS 'Survey ID', 

      A.surveyTitle AS 'Survey Title',  

      'qID' + CAST($(questionID) AS nvarchar(10)) 'Filter',

      CAST(A.tagID AS varchar(20)) AS 'Description ID', 

      A.tagLongText AS 'Description Text', 

      ISNULL(B.COUNT, 0) AS 'Count',

      ISNULL(C.COUNT, 0) AS 'Rolling Average Count'

FROM 

(

      SELECT  SCES.surveyID, SA.surveyTitle, SCES.questionID, SCES.tagID, MTLD.tagLongText

      FROM         SurveyCloseEndedSetup SCES 

      INNER JOIN MasterTagVersion MTV ON MTV.versionID = SCES.versionID

      INNER JOIN mTagLongDesc MTLD ON MTLD.longDescID = MTV.longDescID AND MTLD.langID = $(defaultLangID)              

      INNER JOIN SurveyAccounts SA ON SA.surveyID = SCES.surveyID 

WHERE     

      SCES.surveyid = $(surveyID) AND

      SCES.questionID = $(questionID)

) A LEFT OUTER JOIN

(

SELECT      CCE.tagID, COUNT(CRD.respondentID) AS [COUNT]

FROM  cRespondentData CRD 

      inner join cCloseEnded CCE ON CRD.respondentID = CCE.respondentID 

WHERE

      CRD.surveyID = $(surveyID) AND 

      CCE.questionID = $(questionID) AND

      CRD.submitDateTime BETWEEN '$(beginDate)' AND '$(endDate)'

GROUP BY CCE.tagID

) B ON A.tagID = B.tagID 

LEFT OUTER JOIN

(

SELECT      CCE.tagID, CAST(ROUND(COUNT(CRD.respondentID) * 1.0 / $(avgNumDays), 0) as INT) AS [COUNT]

FROM  cRespondentData CRD 

      inner join cCloseEnded CCE ON CRD.respondentID = CCE.respondentID 

WHERE

      CRD.surveyID = $(surveyID) AND 

      CCE.questionID = $(questionID) AND

      CRD.submitDateTime BETWEEN '$(avgBeginDate)' AND '$(endDate)'

GROUP BY CCE.tagID

) C ON A.tagID = C.tagID 

);

The sql query is working fine, I only need the loop to go through the excel file lines, assign the values to my SurveyID and QuestionID variables and execute the sql for each line in the excel file.

Thank you in advance!

[rwunderlich · Accepted solution]

MyTab:

LOAD

  QuestionId, SurveyID

FROM the.excel.file;

FOR i = 0 to NoOfRows('MyTab')-1

LET surveyID = peek('SurveyID', $(i), 'MyTab');

LET QuestionId= peek('QuestionId', $(i), 'MyTab');

// Put the SQL here or call it as A SUB

NEXT i

-Rob

[Anonymous]

Hi Rob,

Thank you for the solution. It worked fine, but it seems like the loop does not know where to stop. Is there any way we could add a condition that stops it when it reaches an empty cell? Or how is this usually handled in the script?

Here are two screenshots taken during the process of executing it. The first one shows you that it is working, the second shows you the error we get. It does not find any survey ID value and that's why it throws the error.

Thanks again!

Vladimir

[fernando_tonial]

Hi, you need change the for and add "-1".

FOR i = 0 to NoOfRows('MyTab')-1

Best Regards.

Tonial.

[rwunderlich]

I've corrected the post. Made that exact same mistake on two posts yesterday! Maybe I need a vacation...

-Rob