topic Re: I have a code.. How should the variables be defined? in QlikView

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

naziralala — Mon, 12 Jan 2015 00:20:43 GMT

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

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

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,

FROM $(vFile)

(ooxml, embedded labels, table is Experience)

;

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

end if

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

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

petter — Mon, 12 Jan 2015 00:48:02 GMT

You will have to change:

FileList( $(vPath1) )

into either

FileList( '$(vPath1)' )

FileList( vPath1 )

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

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

petter — Mon, 12 Jan 2015 01:00:53 GMT

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.

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

naziralala — Mon, 12 Jan 2015 01:12:41 GMT

Thanks Peter..

now this error...

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

petter — Mon, 12 Jan 2015 01:26:01 GMT

Try changing:

FROM $(vFile)

To:

FROM [$(vFile)]

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

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

naziralala — Mon, 12 Jan 2015 01:56:47 GMT

Thank You Peter...

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

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

naziralala — Mon, 12 Jan 2015 13:08:03 GMT

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

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

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