RE: Keepchar

[Chanty4u]

Hi all,

I want to load multiple excel files from one folder .

ref: RE:Multi load with names

AS folder names like below

'YYYYMM'

ss201601

ab201602

cc201603

dd201604

now i want to load with only date as new column into qlikview

i used below code but its not working?

how to load only  date from the filename?

Multi:

LOAD Account,

credit,

debit,

open,

Date(Date#(keepchar(FileBaseName(),'YYYYMM'),'YYYYMM'),'123456789') AS Date

FROM

(ooxml, embedded labels, table is Sheet1);

[sunny_talwar]

May be this:

Date(Date#(keepchar(FileBaseName(),'123456789'),'YYYYMM'),'YYYYMM') as Date

[sunny_talwar]

The order of KeepChar is before Date and Date# functions, so the arguments for KeepChar should come before Date and Date# functions also

[sunny_talwar]

And bro you missed 0 in keep char

Date(Date#(keepchar(FileBaseName(),'1234567890'),'YYYYMM'),'YYYYMM') as Date

[Chanty4u]

yes sunny i tried below its working fyn.

Multi:

LOAD *,

keepchar(FileBaseName(),'0123456789') AS Date

FROM

(ooxml, embedded labels, table is Sheet1);

Now the issue is ...i have  the folder  with  different  fixed names 

whenever i wan to load  that should loaded to that data only.

if i load those into qlikview   the result  should be like below

Ap  data into one table  and  detailedTB into one table   and  date format should be  every month end of the day  means 201603  wil be  31- 01-2016 how it is possible?

[sunny_talwar]

May be like this:

Ap:

LOAD Account,

credit,

debit,

open,

Date(Floor(MonthEnd(Date#(keepchar(FileBaseName(),'1234567890'),'YYYYMM'))),'DD-MM-YYYY') as Date

FROM

(ooxml, embedded labels, table is Sheet1);

detailedTB:

NoConcatenate

LOAD Account,

credit,

debit,

open,

Date(Floor(MonthEnd(Date#(keepchar(FileBaseName(),'1234567890'),'YYYYMM'))),'DD-MM-YYYY') as Date

FROM

(ooxml, embedded labels, table is Sheet1);

And then if you want to concatenate them, you can do it in resident loads

Multi:

NoConcatenate

LOAD *

Resident Ap;

Concatenate (Multi)

LOAD *

Resident detailedTB;

DROP Tables Ap, detailedTB;

[Chanty4u]

1)date filed is not working sunny... its givng like  ####  .

2)and for example  in future user added  other 4xls files with  AD    ...then how it do automatically to that  table?

[sunny_talwar]

I guess then you should rather look into using loops instead of wild card entries. HIC has proposed a method here:

Loops in the Script

You should then be able to put them all together very easily without creating different tables.

[Clever_Anjos]

Please try

Date(Date#(keepchar(FileBaseName(),'0123456789')&'01','YYYYMMDD')) AS Date

[sunny_talwar]

Clever -

If I may ask, what would be the reason to add 01 at the end? That would still be month start, wouldn't you still need to do MonthEnd() to get 31-01-2016 for example?

UPDATE: or rather

Floor(MonthEnd())