Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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
simone_spanio
Valued Contributor

Re: "Left outer join" for two text files

Look at uppercases!

fc31a6 is not equal to FC31A6


S.

9 Replies
krishna_2644
Valued Contributor III

Re: "Left outer join" for two text files

post a demo document or sample data.

Digvijay_Singh
Honored Contributor III

Re: "Left outer join" for two text files

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

Re: "Left outer join" for two text files

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

simone_spanio
Valued Contributor

Re: "Left outer join" for two text files

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

Re: "Left outer join" for two text files

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.

simone_spanio
Valued Contributor

Re: "Left outer join" for two text files

Look at uppercases!

fc31a6 is not equal to FC31A6


S.

Not applicable

Re: "Left outer join" for two text files

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

Thanks for the help everyone.

Employee
Employee

Re: "Left outer join" for two text files

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
Valued Contributor III

Re: "Left outer join" for two text files

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

Community Browser