Compare two fields from two tables without direct relations

[pacoli2013]

Hello

I have two tables, without a direct relation between them, so no related or equal fields

The first table is an IntervalTable with next content:

PK	Sequence	Code	Description	MinimumIncludedCalendarDays	MaximumIncludedCalendarDays
1	10	-14	< -14 Tage	-999999999999	-15
2	20	-8	-14<=Tage<=-8	-14	-8
3	30	-2	-7<=Tage<=-2	-7	-2
4	40	1	-1<=Tage<=1	-1	1
5	50	2	2<=Tage<=7	2	7
6	60	8	8<=Tage<=14	8	14
7	70	14	> +14 Tage	15	9.999.999.99
8	90	0	Keine Tage	0	0

In my second table I calculate the amounts of days between three dates Postingdate, ShippingDateRequested and ShippingDateConfirmed:

Temp_SalesInvLineShippingDates: LOAD
InvoiceNumber,
Date(PostingDate)                                                 as PostingDate,
Date(ShippingDateRequested)                                as ShippingDateRequested,
Date(ShippingDateConfirmed)                                as ShippingDateConfirmed,
/* create interval days */
(PostingDate - ShippingDateRequested)                  as DaysRequested,
(PostingDate - ShippingDateConfirmed)                  as DaysConfirmed;

SQL SELECT *
FROM SalesInvoice.SalesInvLineShippingDates;

Now I want add one code for Requested (CodeRequested) and one code for Confirmed (CodeConfirmed) into my TempTable both equal the Code from the first table (=IntervalTable) when DaysRequested and DaysConfirmed are between MinimumIncludedCalendarDays and MaximumIncludedCalendarDays.

Is there a command for my TempTable like:

If (IntervalTable. MinimumIncludedCalendarDays <= Days DaysRequested <= IntervalTable. MaximumIncludedCalendarDays, Code, 0) as  CodeRequested

If (IntervalTable. MinimumIncludedCalendarDays <= Days DaysConfirmed <= IntervalTable. MaximumIncludedCalendarDays, Code, 0) as  CodeConfirmed

I watched and searched for solutions on the site but I didn’t found any solution.  I read LOAD DATA INTO QLIKVIEW, but there was not a solution for my problem .

Is there a solution within the script? Or should I solve this problem with Set Analysis?

Thanks in advance

Regards

Court

[kusumanchir]

Hi Court,

Could you please try using intervalmatch().

Refer to the link,

IntervalMatch ‒ QlikView

[Anil_Babu_Samineni]

I am not sure exactly where we are doing? Some how we can achieve something like below

If (IntervalTable. MinimumIncludedCalendarDays >= DaysRequested and IntervalTable.MaximumIncludedCalendarDays <= DaysRequested , Code, 0) as  CodeRequested

[pacoli2013]

Thanks for the idea using Interval Match.

Will test it and Keep you informed

Court