Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

paulyeo11
Valued Contributor II

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
Valued Contributor II

Re: Script work in Qlik View not work in Qlik Sense ?

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

8 Replies
laddu_927
Valued Contributor

Re: Script work in Qlik View not work in Qlik Sense ?

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

rahulpawarb
Valued Contributor II

Re: Script work in Qlik View not work in Qlik Sense ?

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
Valued Contributor II

Re: Script work in Qlik View not work in Qlik Sense ?

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

laddu_927
Valued Contributor

Re: Script work in Qlik View not work in Qlik Sense ?

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

rahulpawarb
Valued Contributor II

Re: Script work in Qlik View not work in Qlik Sense ?

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
Valued Contributor II

Re: Script work in Qlik View not work in Qlik Sense ?

Hi Rahul

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

PAul Yeo

rahulpawarb
Valued Contributor II

Re: Script work in Qlik View not work in Qlik Sense ?

Cheers,

Rahul

paulyeo11
Valued Contributor II

Re: Script work in Qlik View not work in Qlik Sense ?

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>