topic Re: populate date through excel file name in QlikView

populate date through excel file name

vignesh_s — Wed, 25 Nov 2020 16:16:04 GMT

hi every 1,

hi have excel file with file name xyz_23/1/2018.xsl and i have some feild in it excluding date field,i want populate date field with reference to file(xyz_23/1/2018.xsl) and it used happen dynamicly..can any1 help with ths

Re: populate date through excel file name

jonathandienst — Mon, 22 Jan 2018 06:34:30 GMT

A file name cannot contain a '/' character. Are these folders?

Re: populate date through excel file name

shiveshsingh — Mon, 22 Jan 2018 06:36:33 GMT

please try this

date(date#(replace(right(Filename(),13),'.xls',''),'YYYYMMDD'),'YYYYMMDD')

remove '/' from file name, i guess / cannot be a filename

Re: populate date through excel file name

girish2195 — Mon, 22 Jan 2018 06:37:07 GMT

Hi Vignesh,

Date(Date#(TextBetween(FileName(),'_','.'),'DD/M/YYYY'),'DD-MM-YYYY') as Date.

Use the above expression in your script.

Note : Filename format should not change.

Thanks & Regards,

Girish Kumar DV

Re: populate date through excel file name

vignesh_s — Mon, 22 Jan 2018 06:52:11 GMT

sry,the file name looks like XYZ_22012018

ex:

load Value,Quantity,Price

12 ,22 ,100

from XYZ_22012018

concate

load Value,Quantity,Price

13 ,33 ,200

from XYZ_23012018

the above loading file contains 3 fields which are same so it is concatinated

the both file are in the different date"22-01-2018" and "23-01-2018" so i wanna populate date field such that

out put:

Value,Quantity,Price,Date

12 ,22 ,100 22-01-2018

13 ,33 ,200 23-01-2018

Re: populate date through excel file name

vignesh_s — Mon, 22 Jan 2018 06:54:43 GMT

sry,the file name looks like XYZ_22012018

ex:

load Value,Quantity,Price

12 ,22 ,100

from XYZ_22012018

concate

load Value,Quantity,Price

13 ,33 ,200

from XYZ_23012018

the above loading file contains 3 fields which are same so it is concatinated

the both file are in the different date"22-01-2018" and "23-01-2018" so i wanna populate date field such that

out put:

Value,Quantity,Price,Date

12 ,22 ,100 22-01-2018

13 ,33 ,200 23-01-2018

Re: populate date through excel file name

tamilarasu — Mon, 22 Jan 2018 06:58:37 GMT

Something like below.

Data:

Load Value,

Quantity,

Price,

Date(Date#(Right(FileBaseName(),8),'DDMMYYYY'),'DD-MM-YYYY') as Date

From XYZ_22012018;

Concatenate

Load Value,

Quantity,

Price,

Date(Date#(Right(FileBaseName(),8),'DDMMYYYY'),'DD-MM-YYYY') as Date

From XYZ_23012018;

Re: populate date through excel file name

girish2195 — Mon, 22 Jan 2018 06:58:38 GMT

Hi Vignesh,

Use the below code.

load Value,Quantity,Price,Date(Date#(TextBetween(FileName(),'_','.'),'DDMMYYYY'),'DD-MM-YYYY') as Date.

12 ,22 ,100

from XYZ_22012018

concate

load Value,Quantity,Price,Date(Date#(TextBetween(FileName(),'_','.'),'DDMMYYYY'),'DD-MM-YYYY') as Date.

13 ,33 ,200

from XYZ_23012018

Re: populate date through excel file name

rahulpawarb — Mon, 22 Jan 2018 07:24:45 GMT

Hello Vignesh,

Trust that you are doing well!

You can use below given expression to fulfill the requirement.

//Expression

Date(Date#(SubField(FileBaseName(),'_', 2), 'DDMMYYYY'), 'DD-MM-YYYY') AS Date

Decoding:

SubField(FileBaseName(),'_', 2) : This will return the date part from the file name

Date#(SubField(FileBaseName(),'_', 2), 'DDMMYYYY') : This will interpret the extracted part as a date with its present format

Date(Date#(SubField(FileBaseName(),'_', 2), 'DDMMYYYY'), 'DD-MM-YYYY') : This will convert the interpreted date into expected date format.

//Sample Script
Data:
LOAD Value,
    Quantity,
    Price,
    Date(Date#(SubField(FileBaseName(),'_', 2), 'DDMMYYYY'), 'DD-MM-YYYY') AS Date
FROM
Data\XYZ_22012018.xls
(biff, embedded labels, table is Sheet1$);


LOAD Value, 
    Quantity,
    Price,
    Date(Date#(SubField(FileBaseName(),'_', 2), 'DDMMYYYY'), 'DD-MM-YYYY') AS Date
FROM
Data\XYZ_23012018.xls
(biff, embedded labels, table is Sheet1$);

Hope this will help.

Regards!

Rahul Pawar