topic excel calculated formula convert into Qlikview script? in QlikView

excel calculated formula convert into Qlikview script?

devarasu07 — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

How to create below excel calculated column in Qlikview script?

Please find the attached excel source with formula, I'v tried below script using if condition method & it's working but some launch values not correct due to when launch falls in between two week then below if condition gives wrong result.

if(Month=1 or Month=2 or Month=3 , 'Spring'&'-'&Year,

if(Month=4 or Month=5, 'Summer'&'-'&Year,

if(Month=6, 'Summer JIT'&'-'&Year,

if(Month=7 or Month=8 or Month=9, 'Fall'&'-'&Year,

if(Month=10 or Month=11, 'Winter'&'-'&Year,

if(Month=12 ,'Winter JIT'&'-'&Year,

)))))) as Launch;

in the above expression wanted to apply month(min(Date)+3 condition? if i add then script not working(refer the attached document)

FYI

Re: excel calculated formula convert into Qlikview script?

sunny_talwar — Tue, 28 Feb 2017 17:51:32 GMT

May be this:

Fact:

load

if(Month=1 or Month=2 or Month=3 , 'Spring'&'-'&Year(Date+3),

if(Month=4 or Month=5, 'Summer'&'-'&Year(Date+3),

if(Month=6, 'Summer JIT'&'-'&Year(Date+3),

if(Month=7 or Month=8 or Month=9, 'Fall'&'-'&Year(Date+3),

if(Month=10 or Month=11, 'Winter'&'-'&Year(Date+3),

if(Month=12 ,'Winter JIT'&'-'&Year(Date+3),

)))))) as Launch

Resident Temp;

drop table Temp;

Re: excel calculated formula convert into Qlikview script?

sunny_talwar — Tue, 28 Feb 2017 17:55:21 GMT

Or this:

Fact:

LOAD *,

if(Match(Num(Month(Date+3)), 1, 2, 3), 'Spring'&'-'&Year(Date+3),

if(Match(Num(Month(Date+3)), 4, 5), 'Summer'&'-'&Year(Date+3),

if(Match(Num(Month(Date+3)), 6), 'Summer JIT'&'-'&Year(Date+3),

if(Match(Num(Month(Date+3)), 7, 8, 9), 'Fall'&'-'&Year(Date+3),

if(Match(Num(Month(Date+3)), 10, 11), 'Winter'&'-'&Year(Date+3),

if(Match(Num(Month(Date+3)), 12),'Winter JIT'&'-'&Year(Date+3))))))) as Launch

Resident Temp;

Re: excel calculated formula convert into Qlikview script?

vishsaggi — Tue, 28 Feb 2017 17:59:46 GMT

May be this?

MAP:

MAPPING LOAD * INLINE [

Month, Launch

1, Spring

2, Spring

3, Spring

4, Summer

5, Summer

6, Summer JIT

7, Fall

8, Fall

9, Fall

10, Winter

11, Winter

12, Winter JIT

];

Temp:

LOAD *

FROM yourexcelspreadsheet;

Fact:

load

ApplyMap('MAP',Month) & '-' & Year as Launch1

Resident Temp;

drop table Temp;