Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

correctly joining multiple tables

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:

A:

DateTimeA_Value_01A_Value_02
2012/02/1600:00:20a_1a_2
2012/02/1600:01:20a_1a_2
2012/02/1600:02:20a_1a_2

B:

DateTimeB_Value_01B_Value_02
2012/02/1600:00:20b_1b_2
2012/02/1600:01:20b_1b_2
2012/02/1600:02:20b_1b_2
2012/02/1600:03:10b_1b_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):

TimestampA_Value_01A_Value_02B_Value_01B_Value_02
16.02.2012 00:00a_1a_2b_1b_2
16.02.2012 00:01a_1a_2b_1b_2
16.02.2012 00:02a_1a_2b_1b_2
16.02.2012 00:03--b_1b_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??

C:

DateTimeA_Value_01A_Value_02
2012/02/1600:03:10a_1a_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):

TimestampA_Value_01A_Value_02B_Value_01B_Value_02
16.02.2012 00:00a_1a_2b_1b_2
16.02.2012 00:01a_1a_2b_1b_2
16.02.2012 00:02a_1a_2b_1b_2
16.02.2012 00:03a_1a_2
16.02.2012 00:03--b_1b_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

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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;


talk is cheap, supply exceeds demand

View solution in original post

2 Replies
Gysbert_Wassenaar

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;


talk is cheap, supply exceeds demand
Not applicable
Author

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