topic Re: Swapping numbers in QlikView

Swapping numbers

bobbydave — Fri, 20 Nov 2020 18:02:30 GMT

I've a number of dates that I want to look like

201509

201508

201008 etc

The date right now looks like e.g. 15/12/2015 (European format).

I would like it to look like above so I've gotten so far.

=right(purgechar(Calls.Date,'/'),6)

will give me 122015

now I want to get it to look like 201512

I could get both values but how do I reverse concatenate?

right(right(purgechar(Calls.Date,'/'),6) ,2) gives me 12

left(right(purgechar(Calls.Date,'/'),6) ,4) gives me 2015

but how do I reverse it so the 2015 comes out first and then the 12???

Thanks in advance.

Re: Swapping numbers

sunny_talwar — Wed, 09 Sep 2015 08:02:54 GMT

Try this:

Date(Date#(DateField, 'DD/MM/YYYY'), 'YYYYMM') as DateField

Re: Swapping numbers

sasiparupudi1 — Wed, 09 Sep 2015 08:03:15 GMT

try

Date(date#(yourdate,'DD/MM/YYYY'),'YYYYMM')

Re: Swapping numbers

sunny_talwar — Wed, 09 Sep 2015 08:09:34 GMT

Sample script:

Table:

LOAD DateField,

Date(Date#(DateField, 'DD/MM/YYYY'), 'YYYYMM') as NewDateField;

LOAD Date(Today() + RecNo()) as DateField

AutoGenerate 365;

Output:

Re: Swapping numbers

sunilkumarqv — Wed, 09 Sep 2015 08:12:31 GMT

SET DateFormat='M/D/YYYY';

change to

SET DateFormat='YYYYMD';

=SubField(Date,'/',3)&''&SubField(Date,'/',2)&''&SubField(Date,'/',1)

Re: Swapping numbers

qlikviewwizard — Wed, 09 Sep 2015 08:18:48 GMT

Hi Try Like this:


SET DateFormat='M/D/YYYY';


Data:
LOAD *,Date(Date#(DATE_VALUE, 'DD/MM/YYYY'), 'YYYYMM')as DATE_VALUE_Target INLINE
[DATE_VALUE
25/12/2015
15/11/2015
5/10/2015
25/12/2014
15/11/2014
5/10/2014
]

Re: Swapping numbers

bobbydave — Wed, 09 Sep 2015 08:25:34 GMT

Everyone .... I cant believe I asked that question. I do apologise.

I was on holidays for two weeks and overcomplicated something so simple.

Re: Swapping numbers

sunny_talwar — Wed, 09 Sep 2015 08:29:59 GMT

It happens to all of us

But I am glad, all of us were able to quickly help you out.

Best,

Sunny

Re: Swapping numbers

qlikviewwizard — Wed, 09 Sep 2015 08:33:42 GMT

Hi, BobbyDave,

It is a learning dude.