Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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 |
B:
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??
C:
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
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;
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;
Hey, sorry for my late reply. But this worked excellent for me. Thanks and great job!