Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I'm trying to join two statistical data sources from text files. I'm having an issue where all I can seem to make happen is a pure concatenation of the data. Key fields are an autocreated index in each table, and a "mac" address. Each line of data in my stats table has a mac address. I need to create a one to many relationship between this mac address, location, and each stats line in my base stats file so I can quickly see stats data from internal or external sources (my two locations).
My load script:
BaseStats:
LOAD RecNo() AS Index,
mac,
<other stats>
FROM
(txt, codepage is 1252, embedded labels, delimiter is ',', msq, no eof);
Join(BaseStats) (I have tried with and without this statement, results appear to be the same)
LOAD RecNo() AS MacIndex,
mac,
location
FROM
(txt, codepage is 1252, embedded labels, delimiter is ',', msq, no eof);
So Qlik ultimately concat's the data and links the tables, so if I click a "location" it shows the list of mac address, but no associated stats data from the first file. The list of mac addresses grows as a pure addition of DISTINCT count(mac) in the first table, plus the mac's in the second table).
I think I've done this with SQL using WHERE clauses as part of the loads (where x = y), but I've yet to figure out how to use that for two text files.
Any ideas?
post a demo document or sample data.
Try concatenating both the tables using Concatenate keyword and then see the results in front end. I think you should see for a same Mac address, associated other stats, as well as the locations.
I have posted two snippets of data. Not all fields are present in the case of stats, but a few key ones are in each record (mac, ts).
Location list:
mac,location
FC31A6,external
FC31A2,external
FC30CC,external
FC30E6,external
FC30F0,internal
FC319E,internal
FC305C,internal
Stats list:
rxCodec,jitter,tx,val,txLost,hwreg,drop,rx,txCodec,ts,hwpn,mac,avgJitter,key,bld,dts,ver,hwrev,hwname,id,vdt,rxFastUpdateCnt,rxWidth,rxHeight,rxFrameRate,rxActRateKbps,txFastUpdateCnt,txActRateKbps,txWidth,txFrameRate,txHeight
G.722.1C,12,88980,dummy,11,0,2,88965,G.722.1C,2015-10-06T00:00:00.446Z,3111-65290-001,fc31a6,0,callinfo-audio,10681,2015-10-05T23:59:55.000Z,XXX,5,XXX,56130807_27437b0,29-Sep-15 00:32,,,,,,,,,,
H.264-HP,0,,dummy,,0,0,0,,2015-10-06T00:00:00.446Z,3111-65290-001,fc305c,0,callinfo-far-content,10681,2015-10-05T23:59:55.000Z,XXX,5,XXX,56130807_27437b0,29-Sep-15 00:32,0,0,0,0,0,,,,,
H.264-HP,12,580148,dummy,136,0,0,449126,H.264-HP,2015-10-06T00:00:00.446Z,3111-65290-001,fc319e,1,callinfo-video,10681,2015-10-05T23:59:55.000Z,XXX,5,XXX,56130807_27437b0,29-Sep-15 00:32,0,1920,1080,30,2035,782,2993,1920,30,1080
,,127669,dummy,13,0,,,G.722.1C,2015-10-06T00:00:00.478Z,3111-65290-001,fc32a8,,callinfo-audio,10839,1970-01-02T05:24:11.000Z,XXX,5,XXX,17933_2666e50,01-Oct-15 17:48,,,,,,,,,,
G.722,4,5436,dummy,0,,2,3186,G.722,2015-10-06T00:00:01.881Z,3111-65290-001,fc30fc,0,callinfo-audio,XXXX,2015-10-05T23:59:56.000Z,XXX,5,XXX,56130e90_1ebcb00,05-Oct-15 17:37,,,,,,,,,,
,0,,dummy,,,0,0,,2015-10-06T00:00:01.881Z,3111-65290-001,fc30fc,0,callinfo-far-content,XXXX,2015-10-05T23:59:56.000Z,XXX,5,XXX,56130e90_1ebcb00,05-Oct-15 17:37,0,0,0,0,0,,,,,
Hi,
two macs fields are of the same lenght? Did you ensure that there are no spaces in one file and not in the other?
Ad Krishna said, post a sample data.
S.
Actually, I think I just spotted my issue, one file uses upper case, the other file uses lower case for the address field, so Qlik doesn't see them as matching.
I converted both to lower in the load and now it joins up as I would expect.
Look at uppercases!
fc31a6 is not equal to FC31A6
S.
Yes, I just recognized this. I hate posting for help when it's something this simple.
Thanks for the help everyone.
First - avoid the join. It is in most cases unnecessary and can cause both excessive memory usage and record duplication.
Secondly, if the two tables have different fields, they will not be concatenated. They will be associated through the keys (as in your data model viewer). Like a "pending" join.
If you click a "location" and no associated stats are displayed, it just means that you have no stats for this specific location. However, you may have complete disjunct data sets in the key field. If you right-click "location" and "Select All", are then all stats excluded? Or are there stats for some of the mac addresses?
HIC
The Mac field in the 1st table has all caps and the second table mac field has lower lower case letters.
Check Attached and let me know if you were looking for the same..