Mapping values to a range of values in Qlik

[BootCam]

Hi All,

I have the below query from Oracle SQl database:

SQL select
PSLD.BU,
PSLD.YEAR,
PSLD.PERIOD,
PSLD.ID,
PSTN.TND,
PSLD.LGD,
PSLD.AMT
FROM PSLD ,  PSTN ,  PSTF ,  PSGL
where
PSLD.ID IN ('1','2','3','4','5','6','7','8','9','10','11','12','13')
and PSTN.SID = PSTF.SID
and PSTN.TN = PSTF.TN
and PSTN.DATE = PSTF.DATE
and PSTN.TND IN ('A','B','C','D','E','F','G','H','I','J')
and PSTF.TNDNM between PSTN.TNDNM and PSTN.TNDNM_END
and PSTN.TNAM = 'ACT'
and PSLD.ACT between PSTF.RG_FROM and PSTF.RG_TO
AND PSLD.ACT = PSGL.ACT
AND PSLD.LGD = 'ACTUAL'
AND PSLD.YEAR >= 2018
AND PSLD.YEAR <= 2020
AND PSLD.PERIOD <= 12;

I am having a bit difficulties translating the between clause into Qlik script and map values from one table to a range of values to another table.

from Ex: 

PSTF.TNDNM between PSTN.TNDNM and PSTN.TNDNM_END

and PSLD.ACT between PSTF.RG_FROM and PSTF.RG_TO

Is there anyway we can solve this problem using  with/without the use of intervalmatch function in Qlik script?

Thanks,

BC

[TimvB]

Unfortunately Qlik Sense has no between function. You will have to use two where clauses for every between function, i.e.:

PSTF.TNDNM between PSTN.TNDNM and PSTN.TNDNM_END

Will be:

PSTF.TNDNM >= PSTN.TNDNM

and PSTF.TNDNM <= PSTN.TNDNM_END

Hope it helps!

[Rodj]

Hi BC,

There's probably a bunch of different ways to go about this but I'll try to address it in general terms not knowing what your full data model looks like. Typically people shy away from using intervalmatch because it can produce a very large result set and add to load times, particularly if used improperly with large data volumes. if you limit the fields and records involved as much as possible however often it is the best way forward. I don't know where you are at with your scipting skills and understanding of the associative engine but you can do this via creating small temp tables and limiting the data in those via a number of means. You'll find various examples elsewhere in community.

The alternative is to manually do the same thing in some fashion. From what I can see in your case you might look to create a temporary table (i.e. one that we'll drop later) with the result of joining PSTF and PSTN. We only need to include in that table the fields for the "between" operation and the natural keys. Then you can do a load from that temp table with simple > and < where clause to get your between result. You then join your original table back the result, effectively limiting the final result to just those rows that met the between criteria. Don't forget to drop your temp tables when you are done and look out for any auto-concatenation that can screw you up.

Does that make sense?

Rod