topic Re: RE: Keepchar in QlikView

RE: Keepchar

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

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);

Re: RE: Keepchar

sunny_talwar — Fri, 19 Aug 2016 05:47:55 GMT

May be this:

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

Re: RE: Keepchar

sunny_talwar — Fri, 19 Aug 2016 05:48:40 GMT

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

Re: RE: Keepchar

sunny_talwar — Fri, 19 Aug 2016 06:05:49 GMT

And bro you missed 0 in keep char

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

Re: RE: Keepchar

Chanty4u — Fri, 19 Aug 2016 06:09:51 GMT

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?

Re: RE: Keepchar

sunny_talwar — Fri, 19 Aug 2016 06:16:19 GMT

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;

Re: RE: Keepchar

Chanty4u — Fri, 19 Aug 2016 06:24:59 GMT

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?

Re: RE: Keepchar

sunny_talwar — Fri, 19 Aug 2016 06:28:34 GMT

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.

Re: RE: Keepchar

Clever_Anjos — Fri, 19 Aug 2016 11:58:54 GMT

Please try

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

Re: RE: Keepchar

sunny_talwar — Fri, 19 Aug 2016 12:03:26 GMT

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())

Re: RE: Keepchar

Chanty4u — Fri, 19 Aug 2016 12:05:50 GMT

for clever exp it giving me the result as below bro

Re: RE: Keepchar

sunny_talwar — Fri, 19 Aug 2016 12:11:40 GMT

So is this taking you in the right direction? I know you mentioned that my approach gave you ####, but this definitely looks like an improvement

Re: RE: Keepchar

Clever_Anjos — Fri, 19 Aug 2016 12:12:33 GMT

As I´m not using the MonthStart just to make sure that would be 1st.

Re: RE: Keepchar

Chanty4u — Fri, 19 Aug 2016 12:36:03 GMT

yes sunny ... your exp is giving ### .. clever exp working fine.

Re: RE: Keepchar

sunny_talwar — Fri, 19 Aug 2016 12:37:55 GMT

Sounds good, I am not sure why it would be doing that, but as long as you get what you wanted, its all good