How to solve 'unknown file format' in store statem... - Qlik Community

Report Inappropriate Content · ‎2016-08-09

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!

Report Inappropriate Content · ‎2016-08-09

Hey Niclas.

Add square brackets:

STORE Latest INTO ;

BR,

Kuba

Report Inappropriate Content · ‎2016-08-10

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?

marcus_sommer · ‎2016-08-10

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

Report Inappropriate Content · ‎2016-08-10

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

Report Inappropriate Content · ‎2016-08-10

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) ;

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

marcus_sommer · ‎2016-08-10

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

Report Inappropriate Content · ‎2016-08-10

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!

marcus_sommer · ‎2016-08-11

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

MarcoWedel · ‎2016-08-11

$(No_Planned_Orders)

How to solve 'unknown file format' in store statement