topic Re: Create Loop !!!!! in QlikView

Create Loop !!!!!

Wed, 14 May 2014 11:01:57 GMT

Hi All ,

Find the below statement here am repeat data i dont want repeat i want to create loop for that can u suggest me

Temp:

LOAD RespondentID,

Date,

Category,

WeekType,

QID as QuestionID,

Response

FROM

[..\QVD\Cust_Sat_November_Saving.qvd]

(qvd);

Concatenate

LOAD RespondentID as [Respondent ID],

Date,

Category,

WeekType,

QID as QuestionID,

Response

FROM

[..\QVD\Cust_Sat_November_Two_wheeler .qvd]

(qvd);

Data:

NoConcatenate

LOAD RespondentID,

Date,

Category,

WeekType,

//QID as QuestionID,

Response as 101

Resident Temp

Where QuestionID = 101;

Left join

LOAD RespondentID,

// QID as QuestionID,

Response as 102

Resident Temp

Where QuestionID = 102;

Left join

LOAD RespondentID,

Response as 103

Resident Temp

Where QuestionID = 103;

Left join

LOAD RespondentID,

Response as 104

Resident Temp

Where QuestionID = 104;

Left join

LOAD RespondentID,

Response as 105

Resident Temp

Where QuestionID = 105;

Re: Create Loop !!!!!

nizamsha — Wed, 14 May 2014 11:09:26 GMT

LOAD RespondentID,

Date,

Category,

WeekType,

QID as QuestionID,

Response

FROM

[..\QVD\Cust_Sat*.qvd]

(qvd);

for eg

cust_sat1

cust_sat2

cust_sat3

cust_sat4

cust_sat5

cust_sat6

if u have a table name like this in that table u have the same column name then if u use star in the table name then it will load all the table in that location

EG:

[..\QVD\Cust_Sat*.qvd]

(qvd);

Re: Create Loop !!!!!

martinpedersen — Wed, 14 May 2014 11:32:09 GMT

Hi Suneel,

The following script should as far as i can see return the same result as your script


Data:
LOAD RespondentID,
     Date,
     Category,
     WeekType,
     QID as QuestionID,
     Response,
     If(QID=101, Reponse) as 101, 
     If(QID=102, Reponse) as 102, 
     If(QID=103, Reponse) as 103, 
     If(QID=104, Reponse) as 104, 
     If(QID=154, Reponse) as 105
FROM [..\QVD\Cust_Sat_November_*.qvd] (qvd);

Re: Create Loop !!!!!

Wed, 14 May 2014 11:41:31 GMT

Ya it is working but i dont want repeat If condition again and again i have 200 response so it s very difficult can u suggests me any other way by using for statement like:

FOR i = 1 to 150

LET FieldValue = PEEK('RespondentID', 0, 'Temp');

Data:

left JOIN

LOAD

RespondentID ,

Response as '$(FieldValue)'

RESIDENT

Temp

WHERE

Response = '$(FieldValue)';

NEXT ;

Re: Create Loop !!!!!

nizamsha — Wed, 14 May 2014 11:50:14 GMT

For each vFileName in Filelist ('C:\Path\*.txt')

For vFileNo = 1 to NoOfRows('FileListTable')

Let vFileName = Peek('FileName',vFileNo-1,'FileListTable');

Load *,

'$(vFileName)' as FileName

From [$(vFileName)];

Next vFileN0

Re: Create Loop !!!!!

nizamsha — Wed, 14 May 2014 11:50:30 GMT

http://community.qlik.com/blogs/qlikviewdesignblog/2013/09/02/loops-in-the-script

Re: Re: Create Loop !!!!!

martinpedersen — Wed, 14 May 2014 11:54:46 GMT

Data:
LOAD Rowno() as %Id,
  RespondentID,
     Date,
     Category,
     WeekType,
     QID as QuestionID,
     Response
FROM [..\QVD\Cust_Sat_November_*.qvd] (qvd);


For i = 1 to FieldValueCount('QuestionID');
  Let QID = FieldValue('QuestionID', $(i));
  LEFT JOIN
  Load %Id, Response as $(QID)
  Resident Data;
Next

Something like this? It First load you data tables and then join a field for each QuestionId with the responses?