Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello!
I try store a file but get the error:
Unknown file format specificationNo_Planned_Orders
STORE Latest INTO E:\QlikView\Converted\Recommendation_Report_(No_Planned_Orders)_752030820_Aug-07-16_183010.txt.qvd(qvd)
The problem is the parameters () in the path = (No_Planned_Orders).
The path is already set to a variable. So its not that causing the error!
Hey Niclas.
Add square brackets:
STORE Latest INTO
;
BR,
Kuba
Sorry but I get "table not found" error when using the square brackets.
I'm totally clueless how to solve this. Have try to Google and many different solution but nothing works.. Are there any others that have come up with a similar problem and solve that?
This meant that your specified table didn't exist - either there is a typo or these table was automatically concatenated with another previous loaded table. It would be helpful if you post here the relenat parts of your script.
- Marcus
Hey Niclas,
to be 100% sure I tested STORE statement I pasted and it was working fine (it was not without []). As Marcus said - please provide some sample code, so that we can replicate the problem.
BR,
Kuba
Purpose of the script is to convert .txt and .xls files into .qvd files. And doing so by looping true folders.
SET Verbatim=1;
rootfolder:
Load @1:n as path
FROM rootfolder.txt (ansi, fix, no labels, header is 0, record is line);
set vQVD = 'E:\QlikView\Converted\';
/*above path as base path where my year wise folder contains files */
SET vCounter = 0;
LET vFileType = '.txt';
LET vTable = '';
Sub readFiles(root)
if vCounter = 3 or vCounter = 8 or vCounter = 13 then
LET vFileType = '.xls';
END if
For each File in filelist (root & '\*$(vFileType)')
Call getFile(folder);
Latest:
LOAD [File Path],Max([File Time]) as [Max Time] Resident Files Group By [File Path];
Left Join(Latest)
LOAD [File Name] as [Max Time],[File Name] Resident Files;
DROP Table Files;
LET vName = '$(File)';
let vVal = SubField('$(vName)','\','8');
set vB = $(vVal);
LET vCombine=vQVD&vVal;
set vB = $(vCombine);
IF vCounter = 6 then
LET vCombine = 'E:\QlikView\Converted\Sanmina_MRP_Recommendation_Report';
END IF
if vCounter = 3 or vCounter = 8 or vCounter = 13 then
First 1 LOAD filename() as fileName
FROM [$(File)](biff, embedded labels, table is [$(vTable)]);
else
Concatenate
LOAD *, '$(vName)' as [Src File]
From [$(vName)] (utf8, delimiter is '/t', delimiter is '/', delimiter is 'spaces', delimiter is ',', header is 10);
END IF
STORE Latest INTO [$(vCombine).qvd](qvd);
DROP Table Latest;
let vName='';
LET vCounter = ($(vCounter) + 1);
if $(vCounter) = 13 then
EXIT Script;
End if
Call getRootFile
Next File;
End Sub
Sub getFile(folder)
For each File in filelist (root & '\*$(vFileType)')
Files:
LOAD '$(folder)' As [File Path], '$(file)' as [File Name] ,FileTime('$(file)') as [File Time] AutoGenerate(1) ;
next
if vCounter = 3 or vCounter = 8 or vCounter = 13 then
LET vFileType = '.txt';
END if
End Sub
Sub getRootFile
if $(vCounter) = 0 then
Call readFiles(fieldvalue('path', 1) & '\reports\OPS_Report');
ELSEIF $(vCounter) = 1 then
Call readFiles(fieldvalue('path', 1) & '\reports\MDS_Report');
ELSEIF $(vCounter) = 2 then
Call readFiles(fieldvalue('path', 1) & '\reports\Status_Report');
ELSEIF $(vCounter) = 3 then
LET vTable = 'Demand - Planned$';
Call readFiles(fieldvalue('path', 1) & '\reports\Planned_Report');
ELSEIF $(vCounter) = 4 then
Call readFiles(fieldvalue('path', 1) & '\reports\Data_Report');
ELSEIF $(vCounter) = 5 then
call readFiles(fieldvalue('path', 1) & '\reports\Order_Report');
ELSEIF $(vCounter) = 6 then
call readFiles(fieldvalue('path', 1) & '\reports\Recommendation_Report');
ELSEIF $(vCounter) = 7 then
call readFiles(fieldvalue('path', 1) & '\reports\Shipments_Report');
ELSEIF $(vCounter) = 8 then
LET vTable = '7BucketDetail$';
call readFiles(fieldvalue('path', 1) & '\reports\Holds_Report');
ELSEIF $(vCounter) = 9 then
call readFiles(fieldvalue('path', 1) & '\reports\Value_Report');
ELSEIF $(vCounter) = 10 then
call readFiles(fieldvalue('path', 1) & '\reports\Based_Report');
ELSEIF $(vCounter) = 11 then
call readFiles(fieldvalue('path', 1) & '\reports\Revenue_Report');
ELSEIF $(vCounter) = 12 then
call readFiles(fieldvalue('path', 1) & '\reports\All_Value_Report');
ELSEIF $(vCounter) = 13 then
LET vTable = 'APInvoices$';
call readFiles(fieldvalue('path', 1) & '\reports\AP_Report');
End if
End Sub
Call getRootFile
I suggest that you take a look within the logfile to see how often the loop will be executed - which meant to find the position where the script runs into an error and then to look which values the variables have. Also a run within the debugger might here very useful.
- Marcus
First, thank you all for your replies!
Second, I used the debugger and log file. They both pointing out that the file format is wrong in the file the script try to use.
I have used a ugly workaround to solve it. Feels like these "solutions" is the only solutions with QlikView. But they are not so fun to use if you want to make a good and stable script for use in production...
If anyone see this problem and get an great idea on hove to solve it better. Please write it here as a comment!
For me looked your script more complicated than it must be. I would try to avoid those counters and if-loops and query the fileextension within the filelist-loop, for example with: subfield(File, '.', -1) or with another loop through a list of various file-extensions, more like the example in the help by "for each ... next".
- Marcus
$(No_Planned_Orders)