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

[kaushiknsolanki]

Use Concatenate  like below.

Load * from Table A;

Concatenate

Load * from Table B;

[Vaku1606]

Thanks a lot, it works.

But I am struggling with one formula.

LOAD
Quantity,
"Exec. Finish date" as Finish_date,
"Exec. Finish Time" as Finish_time,
"SO no." as SO,
"Order" as WO,
// Timestamp(Date#("Exec. Finish date",'DD.MM.YYYY') + Time#("Exec. Finish Time",'hh:mm:ss') - 0.25, 'DD.MM.YYYY hh:mm:ss') as Date,
Plnt,
"Material Number" as SKU,
"Sold-to pt" as Customer


FROM [lib://O/Produced_Qty.csv]
(txt, utf8, embedded labels, delimiter is ';', msq);

Concatenate

LOAD

Quantity,
"Exec. Finish date" as Finish_date,
"Exec. Finish Time" as Finish_time,
"SO no." as SO,
"Order" as WO,
// Timestamp(Date#("Exec. Finish date",'DD.MM.YYYY') + Time#("Exec. Finish Time",'hh:mm:ss') - 0.25, 'DD.MM.YYYY hh:mm:ss') as Date,
Plnt,
"Material Number" as SKU,
"Sold-to pt" as Customer
FROM [lib://CD/M/Copy of PRODUCED_QTY.xlsx]

Do you know where should I insert this formula with Timestamp, so that it is applied properly to all dates in the script?

Formula is:

Timestamp(Date#("Exec. Finish date",'DD.MM.YYYY') + Time#("Exec. Finish Time",'hh:mm:ss') - 0.25, 'DD.MM.YYYY hh:mm:ss') as Date,

[BrunPierre]

I suggest you keep the date and time separately. keeping the entire DateTime column unnecessarily impacts performance.

For several reasons from this thread Use of Master Calendar, You need to create a separate master calendar dimension table.

Try this

TempFact:
LOAD Quantity,
"Exec. Finish date" as Finish_date,
"Exec. Finish Time" as Finish_time,
"SO no." as SO,
"Order" as WO,
"Pint",
"Material Number" as SKU,
"Sold-to pt" as Customer,
Timestamp(Date#("Exec. Finish date" ,'DD.MM.YYYY') + Timestamp("Exec. Finish Time",'hh:mm:ss')-0.25,'DD.MM.YYYY hh:mm:ss TT') as TempFinishDate

FROM data.xlsx
(ooxml, embedded labels, table is Sheet31);

LOAD Quantity,
"Exec. Finish date" as Finish_date,
"Exec. Finish Time" as Finish_time,
"SO no." as SO,
"Order" as WO,
"Pint",
"Material Number" as SKU,
"Sold-to pt" as Customer,
Timestamp(Date#("Exec. Finish date",'DD.MM.YYYY') + Timestamp("Exec. Finish Time" ,'hh:mm:ss')-0.25,'DD.MM.YYYY hh:mm:ss TT') as TempFinishDate

FROM data.xlsx
(ooxml, embedded labels, table is Sheet31);

Fact:
LOAD *, 
Date(TempFinishDate,'DD:MM:YYYY')as %FinishDate,
Timestamp(TempFinishDate,'hh:mm:ss TT') as [Finish Time]
Resident TempFact;

MasterCalendar: 
Load 
 TempDate AS %FinishDate, 
 week(TempDate) As Week, 
 Year(TempDate) As Year, 
 Month(TempDate) As Month, 
 Day(TempDate) As Day, 
 'Q' & ceil(month(TempDate) / 3) AS Quarter, 
 Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 
 WeekDay(TempDate) as WeekDay 
;

//=== Generate a temp table of dates === 
LOAD 
 date(mindate + IterNo()) AS TempDate
 ,maxdate // Used in InYearToDate() above, but not kept 
WHILE mindate + IterNo() <= maxdate;

//=== Get min/max dates from Field ===/
LOAD
 min(FieldValue('%FinishDate', recno()))-1 as mindate,
 max(FieldValue('%FinishDate', recno())) as maxdate
AUTOGENERATE FieldValueCount('%FinishDate');

DROP Table TempFact;
DROP Field TempFinishDate From Fact;

EXIT SCRIPT;

[Vaku1606]

hello

Thank you for your proposal, but unfortunately it does not work. It seems that format for time is not good and what is the most important is that date is not changing. For example 04.02.2022 02:58:31 after formula is applied should be 03.02.2022 20:58:31.

This is why I believed that time and date should be merged.

Please for advice if you have any idea how to fix this.

Thanks and regards

[BrunPierre]

The sample data just has "02.01.2020", so what's the expected o/p for that specific date. You might as well provide enough data.

[Vaku1606]

Hello,

The purpose of the formula is to decrease 6h. So whatever the date & time are, the new date & time should be -6h. 

For example:

19.09.2022 14:00 should be 19.09.2022 08:00

19.09.2022 02:00 should be 18.09.2022 20:00

Hope it is clear enough. If you need some additional data, please let me know.

Thanks and regards

[BrunPierre]

I modified the script code from the original post and it now looks like this.

[marcus_sommer]

It depends on the number and the kind of transformation-steps on which part such transformation is most suitable. If the both concatenate-loads create already the final table you will need to apply the transformation twice - once in each load. If there are following loads you may apply it a once against the single table.

Beside this you don't create with your approach a date else it would be a timestamp. To get a real date you may do something like this:

date(floor(DATE + TIME - 0.25)) as DATE2

If you set the interpretation-variables appropriately (usually at the beginning of a script but they could be differently set multiple times within the script) you may skip the need of using the conversions-functions of date#() and time#().

- Marcus  

[Vaku1606]

Hello, 

Thank you for your modification. Time works perfectly, but date remains the same - please have a look at the highlighted row:

So in highlighted example date should be changed to 03.02.2020 because time is converting from 2am to 20pm, so it is another date.