Need help in apply map

[#1]

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?

;

[sunny_talwar]

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;

[Digvijay_Singh]

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);