topic Re: Re: Trying To Return A Value Using Set Analysis in QlikView

Trying To Return A Value Using Set Analysis

Tue, 26 Nov 2013 20:07:50 GMT

Hi -

I receive daily files. Each file has an associated file date. Each file contains transactions from multiple dates <= the file date.

I have a calendar I created which lists the file date and the corresponding day of the month. I am trying to return the day of the month which corresponds to the file File Date = the Maximum Transaction Date. The FileDate and TranDate are text stings with the same format MMDDYY, i.e. 112613 is November 26, 2013.

MaxString(TranDate) does return the maximum transaction date, i.e. 112613. I have tested this in a text box.

I was thinking in expression such as the one below might work, but it does not.

=p({<FileDate={MaxString(TranDate)}>} DayofTheMonth)

Thanks,

Mike

Re: Trying To Return A Value Using Set Analysis

swuehl — Tue, 26 Nov 2013 20:31:31 GMT

You can use the p() function only in a set expression within an aggregation function. But I think you don't need p() here.

Try

=only( {<FileDate={$(=MaxString(TranDate))}>} DayofTheMonth)

Re: Trying To Return A Value Using Set Analysis

Tue, 26 Nov 2013 20:40:57 GMT

swuehl -

That doesn't seem to work, and the following portion of the expression you provided is grayed out if that means anything.

$(=MaxString(TranDate))

Thanks,

Mike

Re: Trying To Return A Value Using Set Analysis

swuehl — Tue, 26 Nov 2013 20:47:20 GMT

The dollar sign expansion in gray and italic is fine.

What if you paste the number in:

=only( {<FileDate={112613} >} DayofTheMonth)

Do you get a number? Maybe using single quotes?

=only( {<FileDate={'112613'} >} DayofTheMonth)

If you can have multiple DayOfTheMonth values for the same max FileDate, then only() will return NULL.

If this could be the case, you need to decide which value you want to retrieve, maybe the maxstring:

=maxstring( {<FileDate={112613} >} DayofTheMonth)

Re: Trying To Return A Value Using Set Analysis

Tue, 26 Nov 2013 21:11:48 GMT

Hi swuehl -

Thanks for your quick responses.

All three of these expressions return null "-".

=only( {<FileDate={112613} >} DayofTheMonth)

=only( {<FileDate={'112613'} >} DayofTheMonth)

=maxstring( {<FileDate={112613} >} DayofTheMonth)

But I don't think there are multiple DayofTheMonth values associated with each FileDate, because if I just put =DayofTheMonth in a text box and click on 112613 from a list of file dates, the text box populates with the number I am looking for. This works consistently well for any FileDate I select including the max FileDate.

I've included a copy of the table viewer below is that helps.

Thanks,

Mike

Re: Trying To Return A Value Using Set Analysis

swuehl — Tue, 26 Nov 2013 21:36:47 GMT

Hm, I don't see anything wrong, maybe I am missing something simple.

Could you upload your application?

http://community.qlik.com/docs/DOC-1290

Re: Re: Trying To Return A Value Using Set Analysis

Tue, 26 Nov 2013 23:15:45 GMT

Hi swuehl -

See attached.

Mike

Re: Trying To Return A Value Using Set Analysis

swuehl — Wed, 27 Nov 2013 07:32:21 GMT

It's just a little typo (DayOfTheMonth instead of DayofTheMonth):

=only( {<FileDate={$(=Maxstring(TranDate))}>} DayOfTheMonth)

returns 21.