Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have 2 files stored on 2 different locations:
- File 1 contains data for 2020 and 2021 and
- File 2 contains data for 2022
They look exactly the same as shown below:
Quantity | Exec. Finish date | Exec. Finish Time | SO no. | Order | Plnt | Material Number | Sold-to pt |
7.776,00 | 02.01.2020 | 10:49:59 | 3466323 | 1000907612 | DE | 473549 | 25669 |
7.776,00 | 02.01.2020 | 12:49:46 | 3466323 | 1000907612 | DE | 473549 | 25669 |
4.464,00 | 02.01.2020 | 14:00:52 | 15354258 | 1000907409 | DE | 469655 | 1206 |
3.108,00 | 02.01.2020 | 14:32:25 | 15354623 | 1000907996 | VE | 461872 | 25002 |
How can I merge them into one table in Qlik, so that I have all records for 2020,2021 and 2022?
Thanks in advance and regards
Hello Marcus,
Seems that something is wrong, since DATE2 is not shown at all
@Vaku1606 Revisit the code. You would realized I did a merger of the Date and Time before deriving the new ones.
Hope this helps.
This means that at least one of the fields isn't interpreted as a numeric value probably because you missed to set the interpretation-variables or the conversion-functions date#() + time#() or the there applied format-pattern aren't correct specified. Therefore I suggest you review your script if everything is set properly.
- Marcus
Hi @Vaku1606,
You can just concatenate both tables since they have the same fieldnames. Then you can create a field with a timestamp from the date and time fields and subtract the 6 hours. After subtracting transforming them back to a date and time field. Then you can add a mastercalendar table based on the new date field. The timestamp was just temporarily for creating the new fields and performance will be better if you get rid of the timestamp after the transformation.
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD.MM.YYYY';
SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';
Table:
LOAD *
,Date(TimeStamp([Exec. Finish Time] + [Exec. Finish date], 'DD.MM.YYYY hh:mm:ss') - MakeTime(6)) AS Date
,Time(TimeStamp([Exec. Finish Time] + [Exec. Finish date], 'DD.MM.YYYY hh:mm:ss') - MakeTime(6)) AS Time
;
LOAD * INLINE [
Quantity, Exec. Finish date, Exec. Finish Time, SO no., Order, Plnt, Material Number, Sold-to pt
7776, 02.01.2020, 10:49:59, 3466323, 1000907612, DE, 473549, 25669
7776, 02.01.2020, 12:49:46, 3466323, 1000907612, DE, 473549, 25669
4464, 02.01.2020, 14:00:52, 15354258, 1000907409, DE, 469655, 1206
3108, 02.01.2020, 14:32:25, 15354623, 1000907996, VE, 461872, 25002
9999, 04.02.2022, 02:58:31, 99999999, 9999999999, VE, 999999, 99999
];
-Ruben
Thanks a lot, this works.
Thank you all for your advices/help.