Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All
I have a load script save in QVW , which work fine .
Now i want to implement the same script in Qlik Sense , I get error msg.
The following error occurred:
This statement only works with lib:// paths in this script mode
The error occurred here:
FileList_Sales:
LOAD
FileName_Sales
FROM
C:\TEST\EXCEL\filelist_sales.xlsx
(ooxml, embedded labels, table is SALES)
Can some one advise me ?
1) Create a folder name TEST_ at C Drive :- C:\TEST_
2) Copy the zip file EXCEL to C:\TEST_
3) Unzip the EXCEL file.
4) You can run the ASK0001.QVW
Paul
Hello Paul,
You can use below script:
LOAD * INLINE [
SOURCE, COY
Q_FCPG09,TPG
Q_FCKL09,TKL
Q_DOD_2016,TDS
];
SET vExcelPath = 'lib://Excel/';
SET vRAWPath = 'lib://TEXT/';
FileList_Sales:
LOAD
FileName_Sales
FROM
$(vExcelPath)filelist_sales.xlsx
(ooxml, embedded labels, table is SALES);
FOR i=0 TO NoOfRows('FileList_Sales') -1
LET vFile = PEEK('FileName_Sales', $(i), 'FileList_Sales');
For each vFileName in FileList('$(vRAWPath)*.txt')
if(subfield(subfield('$(vFileName)','\',-1),'.',1) = '$(vFile)') then
sales:
LOAD
FileBaseName() as SOURCE,
FileBaseName()&'-'&@79:82T as Key,
FileBaseName()&'-'&@307:312T as Key_G,
'SALES' as table,
@50:60T as [date],
@10:38T as [COMPANY],
@1:9T as [CUST_ID],
if(@241:248T = '2',@134:147T*-1,@134:147T) as [sales],
@79:82T as [sEG],
@307:312T as [grouping],
@121:133T as [cost]
FROM "$(vFileName)" (ansi, fix, no labels, header is 0, record is line);
endif
Next vFileName
next i
drop Table FileList_Sales;
P.S.: First create two connections for excel and Text files.
Excel Connection:
Name: Excel
Path: C:\TEST\EXCEL\
TEXT Connection:
Name: TEXT
Path: C:\TEST\TEXT\
Hope this will be helpful.
Regards!
Rahul
In qliksense we need library paths . Can you try follow this thread and implement??
Hello Paul,
Please follow below steps:
1. First create a folder type connection (Provide path of an excel file to be loaded and connection name)
2. Post that press Select Data button present under that connection and select the filelist_sales.xlsx file
3. Select desired fields and press insert script button
4. You will get below sample script:
FileList_Sales:
LOAD
FileName_Sales
FROM [lib://Excel/filelist_sales.xlsx]
(ooxml, embedded labels, table is SALES);
Hope this will be helpful.
Regards!
Rahul
Hi Rahul
I do understand how to do lib folder in Qlik Sense. And for Excel folder i have no issue in create lib folder.
My issue is below :-
FROM "$(vFileName)" (ansi, fix, no labels, header is 0, record is line);
endif
Next vFileName
next i
drop Table FileList_Sales;
Hope you can advise me.
Paul Yeo
May be this may help you. Sorry for missing the link Qlik Sense Desktop: Load from path
Hello Paul,
You can use below script:
LOAD * INLINE [
SOURCE, COY
Q_FCPG09,TPG
Q_FCKL09,TKL
Q_DOD_2016,TDS
];
SET vExcelPath = 'lib://Excel/';
SET vRAWPath = 'lib://TEXT/';
FileList_Sales:
LOAD
FileName_Sales
FROM
$(vExcelPath)filelist_sales.xlsx
(ooxml, embedded labels, table is SALES);
FOR i=0 TO NoOfRows('FileList_Sales') -1
LET vFile = PEEK('FileName_Sales', $(i), 'FileList_Sales');
For each vFileName in FileList('$(vRAWPath)*.txt')
if(subfield(subfield('$(vFileName)','\',-1),'.',1) = '$(vFile)') then
sales:
LOAD
FileBaseName() as SOURCE,
FileBaseName()&'-'&@79:82T as Key,
FileBaseName()&'-'&@307:312T as Key_G,
'SALES' as table,
@50:60T as [date],
@10:38T as [COMPANY],
@1:9T as [CUST_ID],
if(@241:248T = '2',@134:147T*-1,@134:147T) as [sales],
@79:82T as [sEG],
@307:312T as [grouping],
@121:133T as [cost]
FROM "$(vFileName)" (ansi, fix, no labels, header is 0, record is line);
endif
Next vFileName
next i
drop Table FileList_Sales;
P.S.: First create two connections for excel and Text files.
Excel Connection:
Name: Excel
Path: C:\TEST\EXCEL\
TEXT Connection:
Name: TEXT
Path: C:\TEST\TEXT\
Hope this will be helpful.
Regards!
Rahul
Hi Rahul
Your instruction very clear. Many thank , it work fine now.
PAul Yeo
Cheers,
Rahul
Hi Rahul
https://community.qlik.com/thread/256946
Can you advise me on the above ?
Paul Yeo
DIrector
TDS Technology (S) P/L
Whatsapp +65 9326 1804
www.tdstech.com<http://www.tdstech.com>