topic Re: Invalid Expression in QlikView

Invalid Expression

Mon, 10 Oct 2016 14:33:53 GMT

Not sure what's going on here in my script.

FINALX:

Load

[PreSequence],

[1103 Position],

[1115 Position],

Math, //created by doing the following [1115 Position] - [1103 Position], I did this in a table above

min(Math) as FinalMath,

[Info Ao]

Resident Final

group by [PreSequence];

Drop Table Final;

Any ideas? My end goal is the following:

For each distinct PreSequence, there could have values for 'Math' that are the following:

PreSequence	Math	Info Ao
123	-6	Pay Bill
123	-2	Pay Service
123	2	Pay Old Bill
123	4	New Service

For each PreSequence, I want to find the Info Ao where the 'Math' value is smallest value that is greater than zero. In this example, that would be 2 and the Info Ao would be 'Pay Old Bill'. Maybe there is an easier way to solve this. I open to suggestions.

Re: Invalid Expression

sunny_talwar — Mon, 10 Oct 2016 15:33:32 GMT

I think you are only grouping by PreSequence here and you still have other non aggregated fields in your load statement is the issue here. Try this may be:

FINALX:

Load

[PreSequence],

Min(Math) as FinalMath,

FirstSortedValye([Info Ao], Math) as [Info Ao]

Resident Final

Where Math > 0

Group By [PreSequence];

Drop Table Final;

Re: Invalid Expression

Mon, 10 Oct 2016 15:53:01 GMT

That did not work. I commented out every line and ran the script through the debugger. It looks as if the something is wrong with the 'Math'(aka FinalMath) field. I created this field bydoing the following in a table before the FINALX table:

Num([1103 Position]) as [1103 Position],

num([1115 Position]) as [1115 Position],

num([1115 Position] - [1103 Position]) as Math,

Re: Invalid Expression

sunny_talwar — Mon, 10 Oct 2016 16:10:14 GMT

I don't see anything wrong in the three lines of code you have posted. Can you share your complete script?

Re: Invalid Expression

Thu, 13 Oct 2016 16:48:27 GMT

I actually figured out another way to do what I wanted. Thank you for the help! I may revisit your idea later, because the way I solved the problem is run time intensive.