Compare two fields from two tables without direct relations

pacoli2013 — Wed, 02 Aug 2017 11:37:05 GMT

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

Re: Compare two fields from two tables without direct relations

kusumanchir — Wed, 02 Aug 2017 11:52:59 GMT

Hi Court,

Could you please try using intervalmatch().

Refer to the link,

IntervalMatch ‒ QlikView

Re: Compare two fields from two tables without direct relations

Anil_Babu_Samineni — Wed, 02 Aug 2017 11:54:39 GMT

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

Re: Compare two fields from two tables without direct relations

pacoli2013 — Wed, 02 Aug 2017 12:04:27 GMT

Thanks for the idea using Interval Match.

Will test it and Keep you informed

Court

topic Re: Compare two fields from two tables without direct relations in QlikView

Compare two fields from two tables without direct relations

Re: Compare two fields from two tables without direct relations

Re: Compare two fields from two tables without direct relations

Re: Compare two fields from two tables without direct relations