topic Re: "Left outer join" for two text files in QlikView

"Left outer join" for two text files

Tue, 13 Oct 2015 13:45:57 GMT

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,

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?

Re: "Left outer join" for two text files

Qrishna — Tue, 13 Oct 2015 13:52:02 GMT

post a demo document or sample data.

Re: "Left outer join" for two text files

Digvijay_Singh — Tue, 13 Oct 2015 13:59:44 GMT

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.

Re: "Left outer join" for two text files

Tue, 13 Oct 2015 14:00:55 GMT

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

Re: "Left outer join" for two text files

simospa — Tue, 13 Oct 2015 14:03:50 GMT

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.

Re: "Left outer join" for two text files

Tue, 13 Oct 2015 14:04:37 GMT

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.

Re: "Left outer join" for two text files

simospa — Tue, 13 Oct 2015 14:05:07 GMT

Look at uppercases!

fc31a6 is not equal to FC31A6

Re: "Left outer join" for two text files

Tue, 13 Oct 2015 14:07:58 GMT

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

Thanks for the help everyone.

Re: "Left outer join" for two text files

hic — Tue, 13 Oct 2015 14:10:46 GMT

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

Re: "Left outer join" for two text files

Qrishna — Tue, 13 Oct 2015 14:16:30 GMT

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