topic Help Mapping Date from Number in QlikView

Help Mapping Date from Number

Tue, 09 Jun 2015 08:24:29 GMT

Hi Guy's

Not sure how to do this but i have tables not joined on date but have numbers like this FREC0001-12, 12 for the year 2012 up to 2015. I would like to map all the numbers with 12 at the end to a year 2012 ect.

Is this possible ?

Regards

Re: Help Mapping Date from Number

alexandros17 — Tue, 09 Jun 2015 08:28:23 GMT

When you load the table create a new field containing the year, if the fields in tha tables have the same name tha tebles will be joined. to do this:

Load

....

myfield

2000 + Right(myField,2) as myYear

....

let me know

Re: Help Mapping Date from Number

petter — Tue, 09 Jun 2015 08:28:49 GMT

If you have a field already denoting the year - like for instance Year then this will link your table with the special field to these years:

LOAD

.......

myField,

'20' & Right( myField , 2 ) AS Year,

....

FROM

.....;

myField containing values like 'FREC0001-12' etc...

Re: Help Mapping Date from Number

Anonymous — Tue, 09 Jun 2015 08:29:00 GMT

can you share your sample application?

Re: Help Mapping Date from Number

Tue, 09 Jun 2015 08:59:09 GMT

Thank you so so much Alessandro

Used

Load

....

myfield

2000 + Right(myField,2) as myYear

and worked perfectly.

Kind regards

Re: Help Mapping Date from Number

MarcoWedel — Tue, 09 Jun 2015 19:38:29 GMT

Hi,

if the presence of the '-' separator is also important, then one solution could be:

LOAD *,
    Year(Date#(SubField(Field1,'-',2),'YY')) as Year
Inline [
Field1
FREC0001-12
FREC0001-13
FREC0002-12
FREC0002-13
FREC0001
FREC0002
FREC0003
];

hope this helps

regards

Marco