9 Replies Latest reply: Oct 13, 2015 10:20 AM by Krishna Nagulapally RSS

    "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

      [D:\pythoncode\stats.txt]

      (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

      [D:\pythoncode\mac list.txt]

      (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
          Krishna Nagulapally

          post a demo document or sample data.

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

            • Re: "Left outer join" for two text files
              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.

              • Re: "Left outer join" for two text files
                Simone Spanio

                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.

                • Re: "Left outer join" for two text files
                  Henric Cronström

                  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