topic Re: Expression is not Working. in QlikView

Expression is not Working.

saurabhwadhwa — Thu, 09 Jun 2016 05:49:21 GMT

Hi All,

The below expression is not working when i write all the calculations (POS , ecommerce ,Fund Transfer , ATM) using Pick and match .

i get the value only for the first calculation i.e. POS (128) and for remaining it shows '-' .

Pick(

match(ValueList('POS','eCommerce','Fund Transfer','ATM')

,'POS','eCommerce','Fund Transfer','ATM')

,Max(Aggr(Sum({<delivery_channel_code={'POS'},txn_date={">=$(=num(today())-30) <$(=num(today()))"}>}txn_cnt),txn_date))

,Max(Aggr(Sum({<delivery_channel_code={'EPOS'},txn_date={">=$(=num(today())-30) <$(=num(today()))"}>}txn_cnt),txn_date))

,Max(Aggr(Sum({$<txn_sub_typ_cd={'DIBXF','DIMIN','DIMOR','DIMOT','DMBPR','DMBRV'},txn_date={">=$(=num(today())-30) <$(=num(today()))"}>}txn_cnt),txn_date))

,Max(Aggr(SUm({<delivery_channel_code={'ATM'},txn_date={">=$(=num(today())-30) <$(=num(today()))"}>}txn_cnt),txn_date))

)

But when i use the individual expression such as POS or EPOS or ATM in straight table or text box ,it works .

Any suggestion ?

Thanks in advance !

Re: Expression is not Working.

Digvijay_Singh — Thu, 09 Jun 2016 10:15:18 GMT

You need to correct set expression syntax, it cannot have expression in the left, along with it basic syntax is also needs correction like use of '<' etc.

Re: Expression is not Working.

sunny_talwar — Thu, 09 Jun 2016 10:19:39 GMT

May be this:

=Pick( match(ValueList('POS','eCommerce','Fund Transfer','ATM') ,'POS','eCommerce','Fund Transfer','ATM'),

Max(Aggr(Sum({<txn_date = {"$(='>=' & Date(Today() - 30, 'DateFieldFormatHere') & '<' & Date(Today(), 'DateFieldFormatHere'))"}>} txn_cnt), txn_date)),

Max(Aggr(Sum({<txn_date = {"$(='>=' & Date(Today() - 30, 'DateFieldFormatHere') & '<' & Date(Today(), 'DateFieldFormatHere'))"}>} txn_cnt),txn_date)),

Max(Aggr(Sum({<txn_date = {"$(='>=' & Date(Today() - 30, 'DateFieldFormatHere') & '<' & Date(Today(), 'DateFieldFormatHere'))"}>} txn_cnt), txn_date)))

Re: Expression is not Working.

saurabhwadhwa — Fri, 10 Jun 2016 02:36:47 GMT

Hi Sunny ,

Thanks for Reply .

Expression works fine when i don't use Aggr() with Pick and Match ,and return some values .if i use the below expression in straight table or text box ,returns some values.

Max(Aggr(Sum({=$(=num(today())-30) }txn_cnt),txn_date))

I don't know why but its behaviour is not normal when i use pick , match and aggr () function in the same expression and returns the value for only the first calculation.

for example :

Pick(

match(ValueList('POS','eCommerce','Fund Transfer','ATM')

,'POS','eCommerce','Fund Transfer','ATM')

,Max(Aggr(Sum({<delivery_channel_code={'POS'},Num_Txndate={">=$(=num(today())-30) <$(=num(today()))"}>}txn_cnt),Num_Txndate))

,Max(Aggr(Sum({<delivery_channel_code={'EPOS'},Num_Txndate={">=$(=num(today())-30) <$(=num(today()))"}>}txn_cnt),Num_Txndate))

,Max(Aggr(Sum({$<txn_sub_typ_cd={'DIBXF','DIMIN','DIMOR','DIMOT','DMBPR','DMBRV'},Num_Txndate={">=$(=num(today())-30) <$(=num(today()))"}>}txn_cnt),Num_Txndate))

,Max(Aggr(Sum({<delivery_channel_code={'ATM'},Num_Txndate={">=$(=num(today())-30) <$(=num(today()))"}>}txn_cnt),Num_Txndate))

)

for this, its giving me value for POS only and for remaining '-'.

Thanks

Re: Expression is not Working.

saurabhwadhwa — Fri, 10 Jun 2016 02:39:55 GMT

Hi Digvijay , Thanks for reply . Expression was incomplete and i updated the expression . Individual expression works fine in straight table or text box and when i use pick ,match and aggr() together . it gives me value for the 1st expression i.e POS in the above example.

Re: Expression is not Working.

sunny_talwar — Fri, 10 Jun 2016 10:50:26 GMT

Would you be able to attach a qvw file to look at?

QlikCommunity Tip: How to get answers to your post?

Preparing examples for Upload - Reduction and Data Scrambling

Uploading a Sample