7 Replies Latest reply: Aug 25, 2017 3:17 AM by Staffan Johansson RSS

    Connect unstructed data

    Staffan Johansson

      Hi, This is my rawdata. What I need to do is to Connect the H-row With the S-rows.

      H-row is an aggregated Level of S-rows(details). I need in some way add ex 17062807211706282341(which is the start and enddate) to the S-rows so I have a key between them.

      All "Blocks" is separated With an "empty row", then next "Block" With a New start-end date 17062823411706291502 comes With Associated S-rows. You dont need to be aware of the data itself, just a way to Connect the H-row With S-rows.

      PS all Blocks does not have common S-rows, sometimes its more or less then 10 as in the example.

       

      H17062807211706282341100000002522243501528090001064160028193
      S00010001000001600000750004421540000000000565149200333880000
      S00020001000001600000750004180420000000000748139100351450000
      S00030001000001600001000004421540000000000520349000408000000
      S00040001000001600001000004180420000000000686839300432450000
      S00050001000002500000750004180540000000000000844200000660000
      S00060001000002500001000004180540000000000000245000000230000
      S00070001000002500001250004180540000000000000136000000110000
      S00100001000003200001000004180540000000000000547400000760000
      S00130001000003800001000004180540000000000000243500000330000
      S00150001000003800001500004180540000000000000139000000220000

       

      H17062823411706291502100000002251142801307550001244210056041
      S00010001000001600000750004421540000000000543148600317180000
      S00020001000001600000750004180420000000000838439100393570000
      S00030001000001600001000004421540000000000338948500263520000
      S00040001000001600001000004180420000000000528939000330390000
      S00090001000003200000750004180540000000000000351000000370000
      S00100001000003200001000004180540000000000000744100000990000
      S00120001000003800000750004180540000000000000343000000370000
      S00130001000003800001000004180540000000000000148000000180000
      S00150001000003800001500004180540000000000000243500000500000
      S00170001000005000001000004180540000000000000248000000480000

        • Re: Connect unstructed data
          Andrew Walker

          Hi Staffan,

           

          Try:

           

          Key:

          LOAD

          AutoNumber(@1,'AutoKeyID') as AutoKeyID,

          @1 as KeyLine

          FROM

          Rawdata.txt

          (txt, codepage is 1252, no labels, delimiter is '\t', msq)

          Where Left(@1,1) = 'H';

           

          [Raw Data]:

          LOAD @1 as Line

          FROM

          Rawdata.txt

          (txt, codepage is 1252, no labels, delimiter is '\t', msq);

           

          For i = 1 to NoOfRows('Raw Data')

          if Left(FieldValue('Line',$(i)) ,1) = 'H' THEN

          Let vHKey =AutoNumber(FieldValue('Line',$(i)),'AutoKeyID');

          END If;

           

          AllData:

          LOAD

          FieldValue('Line',$(i)) as  Line,

          '$(vHKey)' as AutoKeyID

          AutoGenerate 1;

          Next i;

           

          NoConcatenate

          Data:

          LOAD * Resident AllData Where Left(Line,1) <> 'H';

           

           

          DROP tables [Raw Data],AllData;

           

          You get these tables:

           

          Key:

          AutoKeyID KeyLine
          1H17062807211706282341100000002522243501528090001064160028193
          2H17062823411706291502100000002251142801307550001244210056041

           

          Data:

          AutoKeyID Line
          1S00010001000001600000750004421540000000000565149200333880000
          1S00020001000001600000750004180420000000000748139100351450000
          1S00030001000001600001000004421540000000000520349000408000000
          1S00040001000001600001000004180420000000000686839300432450000
          1S00050001000002500000750004180540000000000000844200000660000
          1S00060001000002500001000004180540000000000000245000000230000
          1S00070001000002500001250004180540000000000000136000000110000
          1S00100001000003200001000004180540000000000000547400000760000
          1S00130001000003800001000004180540000000000000243500000330000
          1S00150001000003800001500004180540000000000000139000000220000
          2S00010001000001600000750004421540000000000543148600317180000
          2S00020001000001600000750004180420000000000838439100393570000
          2S00030001000001600001000004421540000000000338948500263520000
          2S00040001000001600001000004180420000000000528939000330390000
          2S00090001000003200000750004180540000000000000351000000370000
          2S00100001000003200001000004180540000000000000744100000990000
          2S00120001000003800000750004180540000000000000343000000370000
          2S00130001000003800001000004180540000000000000148000000180000
          2S00150001000003800001500004180540000000000000243500000500000
          2S00170001000005000001000004180540000000000000248000000480000

           

          Regards

           

          Andrew

          • Re: Connect unstructed data
            Marco Wedel

            Hi,

             

            another solution might be:

             

            QlikCommunity_Thread_272334_Pic1.JPG

             

            tabTemp:
            LOAD RangeSum(Peek(ID),-([@1:n] like 'H*')) as ID,
                Left([@1:n],1) as Type,
                [@1:n] as Line
            FROM [https://community.qlik.com/servlet/JiveServlet/download/1331514-293096/Rawdata.txt] (fix, codepage is 1252)
            Where Len([@1:n]);
            
            table1:
            Generic LOAD * Resident tabTemp;
            
            DROP Table tabTemp;
            

             

            hope this helps

             

            regards

             

            Marco