topic Re: Need help in apply map in QlikView

Need help in apply map

Thu, 26 May 2016 11:50:08 GMT

Hi I have an Inline table below and a data load from a fle

QuarterMonth1:

Mapping LOAD * INLINE [

QQuarterMon, QMMonthName

Q11,Nov

Q12,Dec

Q13,Jan

Q21,Feb

Q22,Mar

Q23,Apr

Q31,May

Q32,Jun

Q33,Jul

Q41,Aug

Q42,Sep

Q43,Oct

];

Temp:

load FlashAsOf;

select Quarter||max(Month) as FlashAsOf from DATABASE where Quarter in (select max(Quarter) from DATABASE group by Quarter;

//Quarter||max(Month) will give an output like 2016Q31

Q31 is nothing but 'May' according to the inline table.

I want to create a variable by doing an applymap

So I created this way

let vFlashAsOf = ApplyMap('QuarterMonth1', Right(peek('FlashAsOf',0,'Temp'),3)) & ' ' & left(peek('FlashAsOf',0,'Temp'),4);

But this is not giving me the result May 2016.

Can somebody help please?

;

Re: Need help in apply map

sunny_talwar — Thu, 26 May 2016 12:36:40 GMT

Try this:

QuarterMonth1:

Mapping LOAD * INLINE [

QQuarterMon, QMMonthName

Q11,Nov

Q12,Dec

Q13,Jan

Q21,Feb

Q22,Mar

Q23,Apr

Q31,May

Q32,Jun

Q33,Jul

Q41,Aug

Q42,Sep

Q43,Oct

];

Temp:

LOAD *,

Date(Date#(QMMonthName & ' ' & Year, 'MMM YY'), 'MMM YY') as Date;

LOAD FlashAsOf,

Left(FlashAsOf, 4) as Year

ApplyMap('QuarterMonth1', Right(FlashAsOf, 3)) as QMMonthName;

select Quarter||max(Month) as FlashAsOf from DATABASE where Quarter in (select max(Quarter) from DATABASE group by Quarter;

Re: Need help in apply map

Digvijay_Singh — Thu, 26 May 2016 12:37:04 GMT

Try like this, you need to avoid applymap for the whole thing, use left function out of applymap -

let vFlashAsOf = ApplyMap('QuarterMonth1', Right(peek('FlashAsOf',0,'Temp'),3)) & '-' & left(peek('FlashAsOf',0,'Temp'),4);

I used below sample -

QuarterMonth1:

Mapping LOAD * INLINE [

QQuarterMon, QMMonthName

Q11,Nov

Q12,Dec

Q13,Jan

Q21,Feb

Q22,Mar

Q23,Apr

Q31,May

Q32,Jun

Q33,Jul

Q41,Aug

Q42,Sep

Q43,Oct

];

Temp:

load * inline [

FlashAsOf

2016Q31,

2015Q13 ];

let vFlashAsOf = ApplyMap('QuarterMonth1', Right(peek('FlashAsOf',0,'Temp'),3)) & '-' & left(peek('FlashAsOf',0,'Temp'),4);