topic Re: date transformation in QlikView

date transformation

0li5a3a — Fri, 12 May 2017 12:05:41 GMT

Hello All,

I need your advise regarding my new challenge. I have two column and I want to concatenate them but i don't want to concatenate all the information.

EX:

order date

12345 12/10/2017

23654 11/11/2016

I want to concatenate the column but I want to achieve this information : 12345121017( order+ DDMMYY).

I tried to convert the date from this format DD/MM/YYYY to DDMMYY but with no success .

Thank you in advance!

Re: date transformation

tresB — Fri, 12 May 2017 12:15:26 GMT

May be like:

order & Date(Date#(date, 'DD/MM/YYYY'), 'DDMMYY') as NewField

Re: date transformation

el_aprendiz111 — Fri, 12 May 2017 15:15:32 GMT

Hi,

1 Example:

EX:
LOAD *,KeepChar(order & date,'0123456789') AS id_New;
LOAD * Inline
[
order,date
12345,12/10/2017
23654,11/11/2016
];
EXIT Script;

Re: date transformation

0li5a3a — Mon, 15 May 2017 07:08:42 GMT

Hi ,

Thanks your your answer but this doesn't works in my case scenario.

Thanks

Re: date transformation

Peter_Cammaert — Mon, 15 May 2017 07:27:07 GMT

Has your date column already been converted to a real QlikView date field? If so, please post an example document with actual data (a few rows is enough). All posted solutions work as expected on the example in your OP. So there may be something else in your actual data that is blocking a correct & efficient transformation using the smallest number of steps possible.

Re: date transformation

ankit777 — Mon, 15 May 2017 07:55:53 GMT

Hi Use this

order&date(date,'DDMMYYYY')

thanks

Re: date transformation

Anonymous — Mon, 15 May 2017 13:21:26 GMT

Hi ,

Try this

=Order&date(Date,'MMDDYY')

Re: date transformation

0li5a3a — Tue, 16 May 2017 07:15:43 GMT

Thank you for your advise. I fixed my problem using txt files.

Is looking like if you try to concatenate 2 column from Excel document the concatenation doesn't concatenate right.

order & Date(Date#(date, 'DD/MM/YYYY'), 'DDMMYY') as NewField this syntax is valid just in txt files not in excel.