Max/Min in script, across all involved tables

[NikoC]

Hi all,

Let's say a field, named DAY, is part for several tables. Thus it will be used as key between those tables.

I'm looking for a nice way to get, in the script part, for instance the Min value of DAY, across all tables which contain that field (and known at this step of the build).

In the visualization side, Day fields are gathered, wherever they come from, but in the script part, it seems every aggregation function has to be related to a table.

Of course, I may look for the min(DAY) of each table and then for the "Min of the Mins", but I would have like to be able to use someting like "Let vMinDate = Min(DAY);" (which by the way doesn't stop the build process but leads to a NULL value). I guess it's because gathering of homonymous fields are only made at the end of the build, but... in case a solution does exist, I'd like to use it

Thanks

[QlikTom]

Try this:

//Put this at the end of your script
SET vSourceField = [DAY];

DayMinMax:
LOAD
	Min(FieldValue('$(vSourceField)', RecNo())) as [MinDay],
	Max(FieldValue('$(vSourceField)', RecNo())) as [MaxDay]
AUTOGENERATE FieldValueCount('$(vSourceField)');

This will create a table named DayMinMax with 2 fields ([MinDay], [MaxDay])

Keep in mind, if [DAY] is a DATE field field, you will want to wrap each expression in a Date() function or you will have the integer representation of that date. 

Help article on the FieldValue function:
https://help.qlik.com/en-US/sense/latest/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/InterRecord...

Help article on the FieldValueCount function:
https://help.qlik.com/en-US/sense/latest/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/InterRecord...

You are correct that in Analysis when a field is referenced it contains values from all tables that contain that field, as a field with the same name in 2 or more tables creates a key)

More information on this topic:
https://community.qlik.com/t5/Qlik-Design-Blog/Symbol-Tables-and-Bit-Stuffed-Pointers/ba-p/1475369

[NikoC]

Hi Tom,

First... Thanks for your answer.

But I've just realized  the reply I made last week, just after your post, seems not to be recorded. Strange since I had received a mail stating a new rank for my profile after replying.

Anyway, thanks again, with a delay!

Coming back on the intial topic, I've done what you suggested but it didn't work. The build works, but the record contained in the DayMinMax table is just "?" and "?".

The involved dimension is a "fake date" seen as an integer (YYYYMMDD), thus no special time function to add to handle it.

Nicolas