topic Re: How to concatenate 3 fields in load script in QlikView

How to concatenate 3 fields in load script

Tue, 25 Feb 2014 08:49:39 GMT

I got these three fields

[bdate],

left([bdate],2) as DD

resident [SIG];

Load

[bdate],

mid([bdate],3,2) as MM

resident [SIG];

Load

[bdate],

mid([bdate],5,2) as YY

resident [SIG];

and I want to make a field that look like this dd-mm-yy

or concatenate DD&'-'&MM&'-'&YY as testdate

but It wont work??

Re: How to concatenate 3 fields in load script

alexandros17 — Tue, 25 Feb 2014 08:55:02 GMT

LOAD

[bdate],
left([bdate],2) & '/' & mid([bdate],3,2) & '/' & mid([bdate],5,2) as testdate

resident [SIG];

Hope it helps

Re: How to concatenate 3 fields in load script

er_mohit — Tue, 25 Feb 2014 09:06:55 GMT

Try this

Table1:

load

[bdate],
left([bdate],2) as DD
resident [SIG];
join

Table2:

Load
[bdate],
mid([bdate],3,2) as MM
resident [SIG];
join

Table3:

Load
[bdate],
mid([bdate],5,2) as YY
resident [SIG];

noconcatenate

Final:

load [bdate],

Date(Date#(DD&MM&YY,'DDMMYY'),'DD-MM-YY') as testdate,

DD&'-'&MM&'-'&YY as testdate2

resident Table1;

drop table Table1;

Re: How to concatenate 3 fields in load script

preminqlik — Tue, 25 Feb 2014 09:17:13 GMT

Hi there , try this :

date(date#(DD&'-'&MM&'-'&YY,'DD-MM-YY'),'DD/MM/YYYY') as FINAL_Date

Re: How to concatenate 3 fields in load script

hic — Tue, 25 Feb 2014 09:18:44 GMT

It looks as if [bdate] is a date in string format. Then I suggest that you don't use string functions to interpret it. It is much better to use the Date#() interpretation function directly:

Date( Date#( bdate, 'DDMMYY' ), 'DD-MM-YY' ) as BDate,

Nothing else.

HIC

Re: How to concatenate 3 fields in load script

Tue, 25 Feb 2014 09:31:12 GMT

hi Allessandro

thanx it works fine

What if I want an age also from the testdate

Load

age(testdate) as age ???????

Re: How to concatenate 3 fields in load script

preminqlik — Tue, 25 Feb 2014 09:51:02 GMT

age(date(today(),'DD/MM/YYYY'),date(testdate,'DD/MM/YYYY')) as Age

Re: How to concatenate 3 fields in load script

Tue, 25 Feb 2014 10:15:16 GMT

OK but it makes a problem with date before 2000

Something with qlikview seeing 63 as 1963 but 64 as 2064