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.
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?
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.