topic Re: Derive new field in QlikView

Derive new field

Anonymous — Wed, 22 Apr 2015 07:21:23 GMT

Hi have contract term,Contract term Details

if i select 42 Months ,in contract Term Details it should show like

Year 1 of 4 years

Year 2 of 4 Years

Year 3 of 4 years

Year 4 of 4 Years

Like this .

how to achieve this

42 Month we need to take as 4 Years

Re: Derive new field

jagan — Wed, 22 Apr 2015 07:29:28 GMT

Hi,

Can you attach some sample data and your expected output? Where you want this in script or in chart?

Regards,

Jagan.

Re: Derive new field

Anonymous — Wed, 22 Apr 2015 07:42:33 GMT

pFA

Re: Derive new field

Anonymous — Wed, 22 Apr 2015 08:04:57 GMT

hi jagan i attached sample application

Thanks in advance

Re: Derive new field

jagan — Wed, 22 Apr 2015 08:31:58 GMT

Hi,

Try this script

Tab1:

LOAD

rowno() as id,

Year,

Month,

[Project Status],

[Supplier(s)],

[Description

Project

Event Name],

Category,

[Sourcing

Lead],

[Business

Owner],

[Budget/Forecast],

[Managed

Spend],

Savings,

[Cost

Avoidance],

[In-Year Savings],

[Savings

%],

[Capital

vs.

Expense] As [Captital vs.Expense],

[Contract Term],

If(WildMatch([Contract Term], '*Months*'), Ceil(KeepChar([Contract Term],0123456789)/12), Num(KeepChar([Contract Term],0123456789))) as MaxNum,

[Savings Year2],

[Savings Year3],

[Savings Year4],

[Savings Year5],

[Savings Year6],

Comments

FROM

[PO Spend and Saving Dashboard\Global_Savings_Executive_View.xlsx]

(ooxml, embedded labels, table is [Raw Data]);

Regards,

Jagan.