Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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.
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
Ok, John.
Change the WHERE clause to include single quotes around the variable:
WHERE [Business Unit] = '$(vFileName)'
It should work now.
Cheers
added the single quotes but when i tried the reload it gave me the message:
Execution of script failed. Reload old data?
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;
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.
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?
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.
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.