How to join 2 tables?

[Vaku1606]

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

[Vaku1606]

Hello Marcus,

Seems that something is wrong, since DATE2 is not shown at all

[BrunPierre]

@Vaku1606  Revisit the code. You would realized I did a merger of the Date and Time before deriving the new ones.

Hope this helps.

[marcus_sommer]

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

[Ruhulessin · Accepted solution]

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

[Vaku1606]

Thanks a lot, this works. 

Thank you all for your advices/help.