Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
paulyeo11
Master
Master

Script work in Qlik View not work in Qlik Sense ?

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

1 Solution

Accepted Solutions
rahulpawarb
Specialist III
Specialist III

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

View solution in original post

8 Replies
Anonymous
Not applicable

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

rahulpawarb
Specialist III
Specialist III

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
Master
Master
Author

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
Not applicable

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

rahulpawarb
Specialist III
Specialist III

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
Master
Master
Author

Hi Rahul

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

PAul Yeo

rahulpawarb
Specialist III
Specialist III

Cheers,

Rahul

paulyeo11
Master
Master
Author

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>