QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Valued Contributor II

Convert if expression to pick(match For Table

Hi All

Table 1 expression work fine.

if

(

match([Pls select 66 Dim],'rEVENUE'),

money(

SUM({<year = {"\$(=Max(year)-0)"},month={"<=\$(=month)"},\$(ColumnDim51)={\$(ColumnDim51)}>}Amount*1)/\$(Columndim89)/1000,

\$(vMoneyFormatK)),

if

(

match([Pls select 66 Dim],'sales'),

money(

sum({\$<year = {\$(=max(year)-0)}, month = {"<=\$(=max({<year={\$(=max(year))}>} month))"}>}\$(ColumnDim51)/\$(Columndim89)/1000),

\$(vMoneyFormatK)),

if

(

match([Pls select 66 Dim],'eXP'),

money(

SUM({<year = {"\$(=Max(year)-0)"},month={"<=\$(=month)"},\$(ColumnDim51)={\$(ColumnDim51)}>}Amount*1)/\$(Columndim89)/1000,

\$(vMoneyFormatK)),

if

(

match([Pls select 66 Dim],'n_PRO'),

money(

SUM({<year = {"\$(=Max(year)-0)"},month={"<=\$(=month)"},\$(ColumnDim51)={\$(ColumnDim51)}>}Amount*1)/\$(Columndim89)/1000,

\$(vMoneyFormatK)),

))))

I try to convert to below format which is using pick match :-

money(pick(

match([Pls select 67 Dim],'rEVENUE','sales','cost','profit','eXP','n_PRO'),

Sum({<\$(ColumnDim67)={\$(ColumnDim67)}>}Amount)/\$(Columndim89)/1000,

Sum({<Year={'>\$(=year(today())-2)'}>} sales/\$(Columndim89)/1000),

Sum({<Year={'>\$(=year(today())-2)'}>} cost/\$(Columndim89)/1000),

Sum({<Year={'>\$(=year(today())-2)'}>} profit_amt/\$(Columndim89)/1000),

Sum({<\$(ColumnDim67)={\$(ColumnDim67)}>}Amount)/\$(Columndim89)*-1/1000,

Sum({<\$(ColumnDim67)={\$(ColumnDim67)}>}Amount)/\$(Columndim89)/1000),

\$(vMoneyFormatK))

My imagine expression as below :-

money(pick(

match([Pls select 66 Dim],'rEVENUE','sales','cost','profit','eXP','n_PRO'),

SUM({<year = {"\$(=Max(year)-0)"},month={"<=\$(=month)"},\$(ColumnDim51)={\$(ColumnDim51)}>}Amount*1)/\$(Columndim89)/1000,

Sum({<Year={'>\$(=year(today())-2)'}>} sales/\$(Columndim89)/1000),

Sum({<Year={'>\$(=year(today())-2)'}>} cost/\$(Columndim89)/1000),

Sum({<Year={'>\$(=year(today())-2)'}>} profit_amt/\$(Columndim89)/1000),

Sum({<\$(ColumnDim66)={\$(ColumnDim66)}>}Amount)/\$(Columndim89)*-1/1000,

Sum({<\$(ColumnDim66)={\$(ColumnDim66)}>}Amount)/\$(Columndim89)/1000),

\$(vMoneyFormatK))

But cannot work. Hope some one can advise me.

Paul

1 Solution

Accepted Solutions
Valued Contributor II

Re: Convert if expression to pick(match For Table

Hi Swuehl

Thank you for your help , i notice that error is not missing bracket , i was some error in high lighted text :-

money(pick(

match([Pls select 67 Dim],'rEVENUE','sales','cost','profit','eXP','n_PRO'),

SUM({<year = {"\$(=Max(year)-1)"},month={"<=\$(=month)"},rEVENUE={rEVENUE}>}Amount*1)/\$(Columndim89)/1000,

Sum({<Year={'>\$(=year(today())-2)'}>} sales/\$(Columndim89)/1000),

Sum({<Year={'>\$(=year(today())-2)'}>} cost/\$(Columndim89)/1000),

Sum({<Year={'>\$(=year(today())-2)'}>} profit_amt/\$(Columndim89)/1000),

Sum({<\$(ColumnDim66)={\$(ColumnDim66)}>}Amount)/\$(Columndim89)*-1/1000,

Sum({<\$(ColumnDim66)={\$(ColumnDim66)}>}Amount)/\$(Columndim89)/1000),

\$(vMoneyFormatK))

Now it work fine.

Paul

2 Replies
MVP

Re: Convert if expression to pick(match For Table

Just from looking at your expression, I think there is a missing closing bracket:

money(

pick(

match([Pls select 66 Dim],'rEVENUE','sales','cost','profit','eXP','n_PRO'),

SUM({<year = {"\$(=Max(year)-0)"},month={"<=\$(=month)"},\$(ColumnDim51)={\$(ColumnDim51)}>}Amount*1)/\$(Columndim89)/1000,

Sum({<Year={'>\$(=year(today())-2)'}>} sales/\$(Columndim89)/1000),

Sum({<Year={'>\$(=year(today())-2)'}>} cost/\$(Columndim89)/1000),

Sum({<Year={'>\$(=year(today())-2)'}>} profit_amt/\$(Columndim89)/1000),

Sum({<\$(ColumnDim66)={\$(ColumnDim66)}>}Amount)/\$(Columndim89)*-1/1000,

Sum({<\$(ColumnDim66)={\$(ColumnDim66)}>}Amount)/\$(Columndim89)/1000)

)

,\$(vMoneyFormatK) )

Valued Contributor II

Re: Convert if expression to pick(match For Table

Hi Swuehl

Thank you for your help , i notice that error is not missing bracket , i was some error in high lighted text :-

money(pick(

match([Pls select 67 Dim],'rEVENUE','sales','cost','profit','eXP','n_PRO'),

SUM({<year = {"\$(=Max(year)-1)"},month={"<=\$(=month)"},rEVENUE={rEVENUE}>}Amount*1)/\$(Columndim89)/1000,

Sum({<Year={'>\$(=year(today())-2)'}>} sales/\$(Columndim89)/1000),

Sum({<Year={'>\$(=year(today())-2)'}>} cost/\$(Columndim89)/1000),

Sum({<Year={'>\$(=year(today())-2)'}>} profit_amt/\$(Columndim89)/1000),

Sum({<\$(ColumnDim66)={\$(ColumnDim66)}>}Amount)/\$(Columndim89)*-1/1000,

Sum({<\$(ColumnDim66)={\$(ColumnDim66)}>}Amount)/\$(Columndim89)/1000),

\$(vMoneyFormatK))

Now it work fine.

Paul