Loop and load from excel

Anonymous — Wed, 05 Mar 2014 21:38:33 GMT

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!

Re: Loop and load from excel

rwunderlich — Thu, 06 Mar 2014 01:12:18 GMT

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

Re: Loop and load from excel

Anonymous — Thu, 06 Mar 2014 15:48:47 GMT

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

Re: Loop and load from excel

fernando_tonial — Thu, 06 Mar 2014 16:08:37 GMT

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

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

Best Regards.

Tonial.

Re: Loop and load from excel

rwunderlich — Thu, 06 Mar 2014 19:40:43 GMT

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

-Rob

topic Re: Loop and load from excel in QlikView

Loop and load from excel

Re: Loop and load from excel

Re: Loop and load from excel

Re: Loop and load from excel

Re: Loop and load from excel