topic Re: lookup based on dimension in QlikView

lookup based on dimension

Sat, 11 Jun 2016 12:30:11 GMT

Hi,

I have a set of transaction data in a stock market. my goal is to find the latest transaction date in Persian (Jalali) format. since qlikview doesn't recognize the date format, I've bound Persian format to Gregorian one, and I'm trying to lookup Persian date with maximum Gregorian date for each customer as dimension. However my tries has been failed.

I've tried combination of only and max in set analysis but it failed since set analysis doesn't care about dimension...

I've also tried FirstSortedValue to no avail since it could be more than just one transaction in the last date.

I'd be glad if anyone knew a solution to the problem.

PS. I have attached a sample file...

Re: lookup based on dimension

swuehl — Sat, 11 Jun 2016 14:27:53 GMT

You can try to use

FirstSortedValue(DISTINCT TransactionPersianDate,-TransactionDate)

This should be ok in your setting, since the persian date should not differ for the same max gregorian date, right?

Re: lookup based on dimension

Sun, 12 Jun 2016 05:23:55 GMT

Yes, the Persian date does not differ for the same Gregorian date. But the problem is FirstSortedValue doesn't accept the DISTINCT parameter...

EDIT: It does accept the parameter but showing error.... weird!! ....

Thank you for your useful response..

Re: lookup based on dimension

swuehl — Sun, 12 Jun 2016 11:04:42 GMT

The syntax checker is not the most reliable part in QlikView.

There were some recent changes in the checker, in my V12 version, it's not marked as error.

But as a general rule, if in doubt, just press OK and check the result of the expression

Re: lookup based on dimension

hic — Tue, 19 Jul 2016 11:38:42 GMT

If you instead create your Persian dates as duals, you can use Max(TransactionPersianDate).

E.g.

Dual(TransactionPersianDate,TransactionDate) as TransactionPersianDate,