topic Re: make date in QlikView

make date

Sun, 28 Aug 2016 17:36:19 GMT

How can you make date from a following field

PerformanceLog_201103206565656565.tmp

How do you extract: 20110320 as date from above in (YYYYMMDD) format.

Thanks

Re: make date

Anil_Babu_Samineni — Sun, 28 Aug 2016 19:01:26 GMT

How about this:

LOAD *,

Date#(KeepChar(Field,'0123'),'YYYYMMDD') as Field1

INLINE [

Field

PerformanceLog_201103206565656565.tmp

];

Re: make date

kaushiknsolanki — Sun, 28 Aug 2016 19:14:56 GMT

Hi,

Try this.

Load *,Date(Date#(Mid(Data,Index(Data,'_')+1,8),'YYYYMMDD')) as DATE inline [

Data

PerformanceLog_201103206565656565.tmp

];

Regards,

Kaushik Solanki

Re: make date

Anil_Babu_Samineni — Sun, 28 Aug 2016 19:17:33 GMT

Kaushik, How about This?

LOAD *,

mid(Field,16, 8 ) as Field1

INLINE [

Field

PerformanceLog_201103206565656565.tmp

];

Re: make date

tamilarasu — Mon, 29 Aug 2016 06:07:07 GMT

Hi Hanns,

You can also try,

Date(Date#(left(Subfield('Field', '_',2),8),'YYYYMMDD'),'YYYYMMDD') as Date

Re: make date

kaushiknsolanki — Mon, 29 Aug 2016 06:22:59 GMT

Hi Anil,

The expression is right, however whenever the text character changes, the script you wrote will fail.

So it is important to get the dynamic value. Have a look at the expression I gave, which is dynamic in nature.

Regards,

Kaushik Solanki

Re: make date

MarcoWedel — Mon, 29 Aug 2016 22:04:21 GMT

Hi,

another solution could be:

table1:
LOAD *,
    Date#(Left(SubField(filename,'_',-1),8),'YYYYMMDD') as date
INLINE [
    filename
    PerformanceLog_201103206565656565.tmp
    PerfLog_201204216565656565.tmp
    Performance_Log_201305226565656565.tmp
];

hope this helps

regards

Marco