correctly joining multiple tables

Wed, 25 Nov 2015 09:45:40 GMT

I am stuck ... I try to join several tables since days and I just don't find a suitable result. My situtaion is that I need to import about 15k text files into one table. Because they vary in their column structure, I loop through different Load-statements. But in one point they all share a date and a time column. So I build a timestamp as a primary key. This works well and the format should be correct. Now the tricky part. Some of the files share timestamps, they have the same primary key. What I need now is that they merge in a table remaining with one row entry. However, they just join under certain circumstances and I want to understand why. Here an example:

Date	Time	A_Value_01	A_Value_02
2012/02/16	00:00:20	a_1	a_2
2012/02/16	00:01:20	a_1	a_2
2012/02/16	00:02:20	a_1	a_2

Date	Time	B_Value_01	B_Value_02
2012/02/16	00:00:20	b_1	b_2
2012/02/16	00:01:20	b_1	b_2
2012/02/16	00:02:20	b_1	b_2
2012/02/16	00:03:10	b_1	b_2

If I join these two tables with following syntax, it works:

Master:

Load * inline [
Timestamp
];

Join (Master) LOAD
      Timestamp(Date(Date#(Date, 'YYYY/MM/DD'), 'DD.MM.YYYY') + left(Time, 5)) as Timestamp,
      A_Value_01,
      A_Value_02
FROM
Source
(ooxml, embedded labels, table is A);

Join (Master) Load
      Timestamp(Date(Date#(Date, 'YYYY/MM/DD'), 'DD.MM.YYYY') + left(Time, 5)) as Timestamp,
      B_Value_01,
      B_Value_02
From
Source
(ooxml, embedded labels, table is B);

Result table (Master):

Timestamp	A_Value_01	A_Value_02	B_Value_01	B_Value_02
16.02.2012 00:00	a_1	a_2	b_1	b_2
16.02.2012 00:01	a_1	a_2	b_1	b_2
16.02.2012 00:02	a_1	a_2	b_1	b_2
16.02.2012 00:03	-	-	b_1	b_2

However when I load information into field A_Value_01 again, it doesn't enter the specific information into the existing row. It creates a new one ... why? And how can I fix this??

Date	Time	A_Value_01	A_Value_02
2012/02/16	00:03:10	a_1	a_2

I add this to my script:

Join (Master) LOAD
      Timestamp(Date(Date#(Date, 'YYYY/MM/DD'), 'DD.MM.YYYY') + left(Time, 5)) as Timestamp,
      A_Value_01,
      A_Value_02
FROM
Source
(ooxml, embedded labels, table is C);

Result table (Master):

Timestamp	A_Value_01	A_Value_02	B_Value_01	B_Value_02
16.02.2012 00:00	a_1	a_2	b_1	b_2
16.02.2012 00:01	a_1	a_2	b_1	b_2
16.02.2012 00:02	a_1	a_2	b_1	b_2
16.02.2012 00:03	a_1	a_2
16.02.2012 00:03	-	-	b_1	b_2

I already had a look on this thread https://community.qlik.com/thread/39177 but couldn't find a solution in this. I start to become desperate of this. Thanks a lot for all replys!

Best

Henrik

Re: correctly joining multiple tables

Gysbert_Wassenaar — Wed, 25 Nov 2015 10:01:01 GMT

Concatenate all the source tables into one table. Then create a new table that aggregates the values per timestamp value

Temp:

LOAD 0 as TimeStamp autogenerate(0);

Concatenate(Temp)

LOAD * FROM *.txt (txt, codepage is 1252, embedded labels, delimiter is ';', msq);

Result:

Noconcatenate LOAD Timestamp,

MaxString(A_Value_01) as A_Value_01,

.....

MaxString(B_Value_02) as B_Value_02

Resident Temp

Group By Timestamp;

Drop Table Temp;

Re: correctly joining multiple tables

Thu, 10 Dec 2015 12:00:04 GMT

Hey, sorry for my late reply. But this worked excellent for me. Thanks and great job!

topic Re: correctly joining multiple tables in QlikView

correctly joining multiple tables

Re: correctly joining multiple tables

Re: correctly joining multiple tables