Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

"Left outer join" for two text files

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?

1 Solution

Accepted Solutions
simospa
Partner - Specialist
Partner - Specialist

Look at uppercases!

fc31a6 is not equal to FC31A6


S.

View solution in original post

9 Replies
krishna_2644
Specialist III
Specialist III

post a demo document or sample data.

Digvijay_Singh

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.

Not applicable
Author

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,,,,,

simospa
Partner - Specialist
Partner - Specialist

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.

Not applicable
Author

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.

simospa
Partner - Specialist
Partner - Specialist

Look at uppercases!

fc31a6 is not equal to FC31A6


S.

Not applicable
Author

Yes, I just recognized this.  I hate posting for help when it's something this simple.

Thanks for the help everyone.

hic
Former Employee
Former Employee

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

krishna_2644
Specialist III
Specialist III

The Mac field in the 1st table has all caps and the second table mac field has lower lower case letters.

Capture1.PNG

Check Attached and let me know if you were looking for the same..