Script work in Qlik View not work in Qlik Sense ?

[paulyeo11]

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

[Anonymous]

In qliksense we need library paths . Can you try follow this thread and implement??

[rahulpawarb]

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

[paulyeo11]

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

[Anonymous]

May be this may help you. Sorry for missing the link Qlik Sense Desktop: Load from path

[rahulpawarb · Accepted solution]

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

[paulyeo11]

Hi Rahul

Your instruction very clear. Many thank , it work fine now.

PAul Yeo

[rahulpawarb]

Cheers,

Rahul

[paulyeo11]

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>