topic Re: How to set the expression to get correct values in QlikView

How to set the expression to get correct values

Wed, 20 Nov 2013 10:16:21 GMT

Hi,

I have to use below expression to get MAX(TransDate) in straight table

Max({<TransTypeDomain={"Receipt"},Transtype={"DD","SO"},AccountTypeID={"19865"}>}TransDate)

when ActualAccountID=AccountID

Could anyone help me how to change the above expression to get correct values. Here ActualAccountID is column in Table1 and AccountID is column in Table2.

Please help me.

Thanks.

Re: How to set the expression to get correct values

salto — Wed, 20 Nov 2013 10:24:00 GMT

Hello,

The syntax is correct - How are Table1 and Table2 linked? Could you Join them?

Re: How to set the expression to get correct values

Wed, 20 Nov 2013 10:28:26 GMT

Yes there is a join between Table1 and Table2. when I am using the abiove expression I am getting incorrect Values.

Please help me how to set this.

Re: How to set the expression to get correct values

Wed, 20 Nov 2013 10:34:26 GMT

Amelia,

I would add in the set Analysis the boolean expression. To do that:

AnotherFieldOfTable1 = {"=(ActualAccountID=AccountID)"}

Fabrice

Re: How to set the expression to get correct values

Wed, 20 Nov 2013 10:39:15 GMT

Thanks and can you explain how to use this as I couldn't able to get what AnotherFieldOFTable1?

Re: How to set the expression to get correct values

jagan — Wed, 20 Nov 2013 10:45:04 GMT

Hi,

Try like this

=Max({<TransTypeDomain={"Receipt"},Transtype={"DD","SO"},AccountTypeID={"19865"}, ActualAccountID = {"=(ActualAccountID=AccountID)"}>}TransDate)

Regards,

Jagan.

Re: How to set the expression to get correct values

Wed, 20 Nov 2013 10:49:53 GMT

Thanks and I used the same still it is showing incorrect dates. Please help how to sort this completely.

Re: How to set the expression to get correct values

Wed, 20 Nov 2013 10:56:05 GMT

I think you have several fields in the Table1 where ActualAccountID is.

in fact, when you have a boolean expression in the set analysis, I stated that you cannot use it a second time. Therefore you have to write

A ={"=(B=C)"}

Fabrice

Re: How to set the expression to get correct values

Wed, 20 Nov 2013 10:57:10 GMT

Thanks and what it means exactly

Re: How to set the expression to get correct values

Wed, 20 Nov 2013 11:00:21 GMT

Do not understand ....

it means that you add in the set analysis one more clause : where the field B= the field C

it was not a point of your request ?

Fabrice

Re: How to set the expression to get correct values

Wed, 20 Nov 2013 11:51:26 GMT

Thanks and when I am testing the result in SQL database it is showing correct values but when I am using in qvw it is showing incorrect values what it means I used below in SQL

select Ref,Max(Transdate) from [DBO].[ACCOUNT]a
left join
[DBO].[TRANSACTIONS]b
on a.accountid=b.accountid
and b.ActualAccountID=a.AccountID
where TransTypeDomain='Receipt' and Transtype in ('DD','SO') and AccountTypeID=('19865')

group by ref
order by ref

Re: How to set the expression to get correct values

er_mohit — Wed, 20 Nov 2013 12:11:56 GMT

Try this may be helps you

Max({<TransTypeDomain={"Receipt"},Transtype={"DD","SO"},AccountTypeID={"19865"},

ActualAccountID=P(AccountID)>}TransDate)