topic Use a formula inside an Inline table in QlikView

Use a formula inside an Inline table

Fri, 07 Aug 2015 23:04:21 GMT

1. Can you insert a formula instead of a hard-coded value to return a value in an inline table?

2. If so, what is the proper syntax?

Ex:

CloseYear:

Load * Inline [

CloseYear, CloseYearCat

2014, 2014

2015, 2015

2014, Delta

2015, Delta

2015, Goal

];

I want to enter a function for CloseYear, something like Year(Today()) and get it to return "2015" instead of having to actually type in "2015" as above.

Is this possible?

Thanks!

Re: Use a formula inside an Inline table

petter — Fri, 07 Aug 2015 23:13:04 GMT

You could instead of having a * after load do the function you mentioned - something like this:

CloseYear:

Load

Pick( Match( CloseYear , 'ThisYear' , 'LastYear' ) , Year( Today() ) , Year( AddMonths( Today() , -12 ))) AS CloseYear,

Pick( Match( CloseYear , 'ThisYear' , 'LastYear' ) , Year( Today() ) , Year( AddMonths( Today() , -12 ))) AS CloseYearCat

Inline [

CloseYear, CloseYearCat

PreviousYear, PreviousYear

ThisYear, ThisYear

PreviousYear, Delta

ThisYear, Delta

ThisYear, Goal

];

Re: Use a formula inside an Inline table

petter — Fri, 07 Aug 2015 23:34:26 GMT

Or maybe like this:

SET YearList = ['TY' , 'LY' , 'Y2', 'Y3', 'Y4' ];

LOAD

Year( Today() ) - Match( Y1 , $(YearList) ) + 1 AS Y1,

Year( Today() ) - Match( Y2 , $(YearList) ) + 1 AS Y2

INLINE [

Y1, Y2

TY, LY

TY, TY

TY, Y4

LY, Y3

];

Re: Use a formula inside an Inline table

Kushal_Chawda — Sat, 08 Aug 2015 05:57:23 GMT

Something like this

Load *, Evaluate(CloseYear) as CloseYearNew,

Evaluate(CloseMonth) as CloseMonthNew

Inline [

CloseYear,CloseMonth

year(today()),month(today())

];

Re: Use a formula inside an Inline table

Tue, 18 Aug 2015 22:17:33 GMT

Thanks!

Re: Use a formula inside an Inline table

Tue, 18 Aug 2015 22:17:41 GMT

Thanks!

Re: Use a formula inside an Inline table

Anonymous — Thu, 02 Feb 2017 15:05:41 GMT

Thank you Kushal!! This was exactly what I was looking for!!