Script Loop

[#1]

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.

[Anonymous]

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.

[#3]

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

[Anonymous]

Ok, John.

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

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

It should work now.

Cheers

[#5]

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

Execution of script failed. Reload old data?

[maxgro · Accepted solution]

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;

[#7]

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;

NEXT

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.

[#8]

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?

[maxgro]

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.

[#10]

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.