How to concatenate 3 fields in load script

[#1]

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??

[alexandros17 · Accepted solution]

LOAD

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

resident [SIG];

Hope it helps

[er_mohit]

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;

[preminqlik]

Hi there , try this :

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

[hic]

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

[#6]

hi Allessandro

thanx it works fine

What if I want an age also from the testdate

Load

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

[preminqlik]

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

[#8]

OK but it makes a problem with date before 2000

Something with qlikview seeing 63 as 1963 but 64 as 2064