Issues with MinString and Strings with lower/upper cases

[EliGohar]

Hi all,

Using the following expression I'm trying to get the first source (ABC order) with the maximum date (with a URL that is not null):

MinString({<Date={"<=$(=Date(Max(Date),'DD/MM/YYYY'))"}, [Dwell_Heat_Map_URL]-={"-"}>} Source)

This is the list of sources I have

What I get in that case is Camera1112, which is not the min string. I suppose to get airport1 source.

I guess it's something to do with Upper and lower cases.

Can you advise me how to overcome this issue?

Thanks 🙂

[EliGohar]

I wish I could but it's not possible (Workplace policy)

[sunny_talwar]

Is your date field only contain date or is it a timestamp by any chance?

[EliGohar]

Already verified it, it includes only date

[sunny_talwar]

I am not sure what to tell you. I can't see of a reason for it to not work... but I might be missing something. May be someone else can help as I am out of ideas. I could have helped, if I was able to play around with it, but just by looking at the expression... nothing sticks out.

[EliGohar]

I really appreciate your efforts to help.
I don't understand Qlik's behavior in this case.
Why the script can't treat lower and upper cases the same?
Why "C" is not the same as "c" when trying to sort by string in expression using Maxstring/Minstring?

[sunny_talwar]

I think this is because C and c have different underlying values... check this out

='C has a value of ' & Ord('C') & ' and' & Chr(10) & 'c has a value of ' & Ord('c')

Here C = 67, where as c = 99.

[rogerpegler]

A light tweak to Sunny's great work:

MinString({<Date={"<=$(=Date(Max(Date),'DD/MM/YYYY')) >=$(=Date(Max(Date),'DD/MM/YYYY')) "}, [Common_Path_URL]-={"-"}>} Capitalize(Source))

I have previously found set analysis won't  always accurately select a single day for unknown reasons, so an approach which includes <= the day and >= the day results in a set that includes the single day .

[EliGohar]

@rogerpeglerthanks but it returns nothing.

And to anybody who tries to help - You can ignore lower/upper cases

Can you tell me how to retrieve the max date and return his source name using set analysis?

In the following screenshot, I want to get "Instersection 1" because it has the max date in the table.

Date format is DD/MM/YYYY

[sunny_talwar · Accepted solution]

Not entirely sure if this helps in anyway... but I created a sample dashboard where I loaded 4 rows that you have showed in the image above

And using the expression that we used before...

MinString({<Date={"$(=Date(Max(Date),'DD/MM/YYYY'))"}, [Common_Path_URL]-={"-"}>} Capitalize(Source))

I got this

Now, if it works for me, I am not sure what you have differently which makes it not work for you, but one thing which is sort of the ordinary is that you load you say that you load your date as DD/MM/YYYY, but display it as MM/DD/YY? I mean this is not too bad, but when it comes to set analysis... things tend to get complicated with the date formats. Set analysis requires you to have the same date format as it was loaded, else it won't work. Are you 100% confident that you load the date as DD/MM/YYYY? One way to change this to load your date like this

Date(Floor(DateField)) as Date

and now just use this

MinString({<Date = {"$(=Date(Max(Date)))"}, [Common_Path_URL]-={"-"}>} Capitalize(Source))  

[EliGohar]

@Hi @sunny_talwar 

If you change your expression from MinString to MaxString, do you get Intersection1 ? or airport1?

Thanks,

Eli.