Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Bucharest on Sept 18th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
naziralala
Creator
Creator

I have a code.. How should the variables be defined?

set vPath1='C:\Akshay\Qlik_Test\SourceDocs\External Files\*.xlsx';

set vPath2='C:\Akshay\Qlik_Test\SourceDocs\Shared QVD\';

set vFile='.xlsx';

for each vFile in FileList($(vPath1))

     let vQVD = replace(vFile, '.xlsx', '.qvd');

     let x = TextBetween(vQVD,'C:\Akshay\Qlik_Test\SourceDocs\External Files\QVTest Database ','.qvd');

     if alt(FileSize('$(vQVD)'), 0) = 0 then

          Customer:

          LOAD

              '$(x).'&Id as Id,

               Name

          FROM $(vFile)

          (ooxml, embedded labels, table is Customer)

          ;

          STORE Customer INTO $(vPath2)Customer.qvd (qvd);

          //DROP TABLE Customer;

     end if

next

for each vFile in FileList($(vPath1))

     let vQVD = replace(vFile, '.xlsx', '.qvd');

     let y = TextBetween(vQVD,'C:\Akshay\Qlik_Test\SourceDocs\External Files\QVTest Database ','.qvd');

     if alt(FileSize('$(vQVD)'), 0) = 0 then

          Product:

          LOAD

              '$(y).'&[Id (PK)] as PID,

               Name,

               ProductBrandId

          FROM $(vFile)

          (ooxml, embedded labels, table is Product)

          ;

          STORE Product INTO $(vPath2)Product.qvd (qvd);

     end if

next

for each vFile in FileList($(vPath1))

     let vQVD = replace(vFile, '.xlsx', '.qvd');

     let z = TextBetween(vQVD,'C:\Akshay\Qlik_Test\SourceDocs\External Files\QVTest Database ','.qvd');

     if alt(FileSize('$(vQVD)'), 0) = 0 then

          Experience:

          LOAD

              Id,

              CustomerId,

              '$(z).'&CustomerId as NewCustomerID,

              ProductId,

              '$(z).'&ProductId as NewProductID,

              '$(z).'&Id&CustomerId&ProductId as KEY,

              Date,

              Sales,

              Rebate,

              Units,

              H

          FROM $(vFile)

          (ooxml, embedded labels, table is Experience)

          ;

          STORE Experience INTO $(vPath2)Experience.qvd (qvd);

     end if

next

DROP TABLE Customer;

DROP TABLE Product;

DROP TABLE Experience;

===========================================================================================

I have also included the error snapshot below and also, vQVD should be defined...

If so how?

Regards,

Nazira

6 Replies
petter
Partner - Champion III
Partner - Champion III

You will have to change:

  FileList( $(vPath1) )


into either

FileList( '$(vPath1)' )


or


   FileList( vPath1 )


and the same for the other uses of FileList( .... )

petter
Partner - Champion III
Partner - Champion III

The reason your    FileList ( $(FilePath1) )    doesn't work is that it expands the string variable into this:

       FileList ( C:\Akshay\Qlik_Test\SourceDocs\External Files\*.xlsx  )

    and it needs to be a string ... which it isn't because it is not enclosed in single-quotes and it is not a variable

    name either after the $-sign expansion....

      Whereas:       FileList( '$(FilePath1)' )   expands into 

                           FileList( 'C:\Akshay\Qlik_Test\SourceDocs\External Files\*.xlsx' )

      and:    FileList( FilePath1 ) will not expand, but FilePath1 is already a string variable that FileList will

                accept.

naziralala
Creator
Creator
Author

Thanks Peter..

now this error...

petter
Partner - Champion III
Partner - Champion III

Try changing:

      FROM $(vFile)

To:

    FROM [$(vFile)]

If that doesn't work then your xlsx file might be corrupt....

naziralala
Creator
Creator
Author

Thank You Peter...

It worked...You are the best...

naziralala
Creator
Creator
Author

Hi Peter,

I have added more code, but the results on customer are not correct...

I am adding the complete code from the beginning...

set vPath1='C:\Akshay\Qlik_Test\SourceDocs\External Files\*.xlsx';

set vPath2='C:\Akshay\Qlik_Test\SourceDocs\Shared QVD\';

set vFile='C:\Akshay\Qlik_Test\SourceDocs\External Files\*.xlsx';

for each [$(vFile)] in FileList('$(vPath1)')

     let vQVD = replace(vFile, '.xlsx', '.qvd');

     let x = TextBetween(vQVD,'C:\Akshay\Qlik_Test\SourceDocs\External Files\QVTest Database ','.qvd');

     if alt(FileSize('$(vQVD)'), 0) = 0 then

          Customer:

          LOAD

              '$(x).'&Id as Id,

               Name

          FROM $(vFile)

          (ooxml, embedded labels, table is Customer)

          ;

          STORE Customer INTO $(vPath2)Customer.qvd (qvd);

          //DROP TABLE Customer;

     end if

next

for each [$(vFile)] in FileList('$(vPath1)')

     let vQVD = replace(vFile, '.xlsx', '.qvd');

     let y = TextBetween(vQVD,'C:\Akshay\Qlik_Test\SourceDocs\External Files\QVTest Database ','.qvd');

     if alt(FileSize('$(vQVD)'), 0) = 0 then

          Product:

          LOAD

              '$(y).'&[Id (PK)] as PID,

               Name,

               ProductBrandId

          FROM $(vFile)

          (ooxml, embedded labels, table is Product)

          ;

          STORE Product INTO $(vPath2)Product.qvd (qvd);

     end if

next

======the above code generates QVD

Customer:

LOAD Id as CustomerId,

     Name as CustomerName

FROM

(qvd);

Product:

LOAD PID,

     Name as ProductName,

     ProductBrandId

FROM

(qvd);

Experience:

LOAD Id,

     CustomerId,

     ProductId as PID,

     Date(Date,'M/D/YYYY') as Date,

     month(Date) as Month,

     Year(Date) as Year,

     Sales,

     Rebate,

     if(Rebate=0,'No','Yes') as RebateFlag,

     Units

    

FROM

(qvd);

Quarter:

LOAD

'Quarter'&If(Month(QuarterStart(Date))='1',4,Div(Month(QuarterStart(Date)),3)) as Quarter

Resident Experience;

==================the above code loads data from qvd..

I want data to appear as A.C1 as Customer id from Database Table A and B.C1 coming from table B

But the data is not coming properly is number one problem...

Please assist...