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
