topic Re: Re: Script Loop in QlikView

Script Loop

Thu, 19 Jun 2014 13:41:17 GMT

I am trying to figure out the best way to loop through a single field of Business Units in order to separate my data into separate QVDs based on each business unit in the field. Right now my code is:

FOR vFileNo = 1 to NoOfRows('TableName')

Let vFileName = Peek('Business Unit',vFileNo-1,'TableName');

Load *,

'$(vFileName)' as FileName

FROM $(vRoot)Data\QVDs\TableName_.qvd (qvd);

WHERE [Business Unit] = $(vFileName);

STORE TableName into $(vRoot)Data\QVDs\TableName_$(vFileName).qvd (qvd);

NEXT vFileNo

However this does not seem to be working. Is there another, better way that will work?

Thank you.

Re: Script Loop

Anonymous — Thu, 19 Jun 2014 13:51:24 GMT

Hello, John.

I think your code is ok and should work. What is the result you are getting? No qvd stored? Some kind of error?

How did you create the table TableName from which you peek the Business Unities? In case you load it from TableName_qvd, did you remember the load distinct? TableName should have only distinct values of Business Unities.

Please, provide more information so we can better help you.

Regards.

Re: Script Loop

Thu, 19 Jun 2014 15:05:33 GMT

I keep getting an error message:

Field not found - <COMPANY>

Load *,

'COMPANY US' as FileName

FROM C:\Users\jstickne\Desktop\Applications\Data\QVDs\TableName_.qvd (qvd)

WHERE [Business Unit] = COMPANY US

And I did load it from TableName_.qvd, so would I do load distinct like:

TableName:

LOAD DISTINCT

Business Unit

FROM

$(vRoot)Data\QVDs\ConcurExtract_.qvd (qvd);

Thank you

Re: Script Loop

Anonymous — Thu, 19 Jun 2014 15:09:59 GMT

Ok, John.

Change the WHERE clause to include single quotes around the variable:

WHERE [Business Unit] = '$(vFileName)'

It should work now.

Cheers

Re: Script Loop

Thu, 19 Jun 2014 15:58:08 GMT

added the single quotes but when i tried the reload it gave me the message:

Execution of script failed. Reload old data?

Re: Script Loop

maxgro — Thu, 19 Jun 2014 17:02:09 GMT

if I understand your question

// some test data
TableName:
Load
TransLineID,
TransID,
mod(TransID,26)+1 as Num,
Pick(Ceil(3*Rand1),'A','B','C') as Dim1,
Pick(Ceil(6*Rand1),'a','b','c','d','e','f') as Dim2,
Pick(Ceil(3*Rand()),'X','Y','Z') as Dim3,
Round(1000*Rand()*Rand()*Rand1) as Expression1,
Round(  10*Rand()*Rand()*Rand1) as Expression2,
Round(Rand()*Rand1,0.00001) as Expression3;
Load
Rand() as Rand1,
IterNo() as TransLineID,
RecNo() as TransID
Autogenerate 10
While Rand()<=0.5 or IterNo()=1;

// store and drop test data
STORE TableName into $(vRoot)\QVDs\TableName_.qvd (qvd);
DROP Table TableName;

// root folder
set vRoot='.';


// values of field Dim1 needed in for loop to make files
DimTable:
load Distinct Dim1 FROM $(vRoot)\QVDs\TableName_.qvd (qvd);


// loop on Dim1 field values
FOR vFileNo = 1 to NoOfRows('DimTable')

  Let vFileName = Peek('Dim1',vFileNo-1,'DimTable');
  TRACE *****$(vFileName)*****;

  // load filtered (by Dim1) data from qvd
  TableName:
  Load
  *,
  '$(vFileName)' as FileName
  FROM $(vRoot)\QVDs\TableName_.qvd (qvd)
  WHERE Dim1 = '$(vFileName)';

  // store filtered data
  STORE TableName into $(vRoot)\QVDs\TableName_$(vFileName).qvd (qvd);
  DROP Table TableName;

NEXT vFileNo;

Re: Script Loop

Thu, 19 Jun 2014 17:30:41 GMT

Hi,

You can try using a variable with values of [Bussines Unit] Field and then use a for each loop, something like this:

Example 1:

FOR Each a in 1,3,7,'xyz'

LOAD * FROM file$(a).csv;

Example 2:

// lista de todos los archivos relativos a QV en disco

SUB DoDir (Root)

FOR Each Ext in 'qvw', 'qva', 'qvo', 'qvs'

FOR Each File in filelist (Root&' \*.' &Ext)

LOAD

'$(File)' as Name,

FileSize( '$(File)' ) as Size,

FileTime( '$(File)' ) as FileTime

autogenerate 1;

NEXT File

NEXT Ext

FOR Each Dir in dirlist (Root&' \*' )

call DoDir (Dir)

NEXT Dir

ENDSUB

CALL DoDir ('C:')

Example3:

LET vBussinesUnit = 'BU1', 'BU2', 'BUN';

FOR Each vBU in '$(vBussinesUnit)'

Load

'$(vBussinesUnit)' as FileName

from myTable;

STORE myTable into '$(vBussinesUnit)'.qvd;

DROP Table '$(vBussinesUnit)';

NEXT vBU

Best regards.

Re: Script Loop

Thu, 19 Jun 2014 17:52:04 GMT

Thanks! This worked perfectly. However I get the message "Execution of script failed. Reload old data?" even though the data in the qvds that were created was correct. Is there a way to not get this message?

Re: Re: Script Loop

maxgro — Thu, 19 Jun 2014 18:08:37 GMT

I don't get the message when I run this script in my environment (qvw and qvd in the attachment)

Sometimes (with other scripts) I get the same message; usually because qlik can't write a qvd file or because of some crashed qv.exe locking a qvd.

Re: Re: Script Loop

Thu, 19 Jun 2014 19:05:32 GMT

Would you know a reason why some of the Business Units wouldn't be loaded? I went back to look at 3 BUs didn't have a qvd created for them. That could be why it crashed.