topic Re: Convert if expression to pick(match For Table in QlikView

Convert if expression to pick(match For Table

paulyeo11 — Fri, 11 Mar 2016 15:01:00 GMT

Hi All

Table 1 expression work fine.

(

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

money(

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

$(vMoneyFormatK)),

(

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

money(

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

$(vMoneyFormatK)),

(

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

money(

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

$(vMoneyFormatK)),

(

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

Re: Convert if expression to pick(match For Table

swuehl — Fri, 11 Mar 2016 15:18:15 GMT

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

Re: Convert if expression to pick(match For Table

paulyeo11 — Fri, 11 Mar 2016 15:43:23 GMT

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