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

[naziralala]

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

[petter]

You will have to change:

  FileList( $(vPath1) )

into either

FileList( '$(vPath1)' )

or

   FileList( vPath1 )

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

[petter]

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]

Thanks Peter..

now this error...

[petter]

Try changing:

      FROM $(vFile)

To:

    FROM [$(vFile)]

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

[naziralala]

Thank You Peter...

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

[naziralala]

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...