5 Replies Latest reply: Nov 7, 2012 6:05 AM by ozan.can RSS

    CrossTable or Join or Something

      Hello there. I need some help.

      This is the situation:

      I have one table named Messages. It contains some messages.

      Messages.csv

      1.jpg

       

      When received a new message, it is inserted the table with Status=1. Then, when cleared the message it is inserted the table again with Status=0. (eg. Top 2 records)

      This table what I need.

       

      2.jpg

       

      I want to show each related message in one row. I tried many approach. Join, CrossTable, some script etc. But I couldn't make it. I'am stuck. Thank you

        • Re: CrossTable or Join or Something

          Hai,

           

           

          use interval match

           

          Thanks.

          • Re: CrossTable or Join or Something

            Hi

             

            I tried but i didnt get it

            it is interesting ,if u get plz share it to me

            i get below values

             

             

            im using script like this

             

            Times:

            LOAD line,

                 msgno,

                 message,

                 time,

                 status

            FROM

            C:\Users\Desktop\time1.xlsx

            (ooxml, embedded labels, table is Sheet1)

            ;

            Times1:

            LOAD distinct line,

                 msgno,

                 message,

                 time as startdate Resident Times Where status=1;

               

             

             

            Join(Times1)

             

             

            LOAD Distinct line,

                 msgno,

                 message,

                 time as enddate Resident Times Where status=0;

             

            below is my result

             

             

            line message msgno startdate enddate
            1 msg5 5 10:05 10:10
            1 msg5 5 10:05 10:37
            1 msg5 5 10:24 10:10
            1 msg5 5 10:24 10:37
            1 msg12 12 10:20 10:30
            1 msg6 6 10:22
            2 msg5 5 10:32
              • Re: CrossTable or Join or Something

                Yes, I got this result before. I divided two tables then joined the tables. When I use Join or Left Join, records are being related as many to many. Each record which has Status=1 must join First 1 record which is Status=0, the same MsgNumber and closest Time (the point).

                 

                I am still on it. It is realy complicated. I am triying with intervalmatch right now.

                Thanks for your trying. Regards.

              • Re: CrossTable or Join or Something

                I divided two tables. One of them includes Status=1 (ReceivedAlarmsTable), another one includes Status=0 records.

                 

                //

                ReceivedAlarmsTable:

                LOAD

                     Line & MsgNumber as Key,

                     Line,

                     MsgNumber,

                     MsgText,

                     Time_ms,

                     Status

                FROM

                [logdata\QLog2.csv]

                (txt, codepage is 1254, embedded labels, delimiter is ';')

                Where Status = 1;

                  

                ClearAlarmsTable:

                LOAD

                     Line & MsgNumber as Key,

                     Line,

                     MsgNumber,

                     Time_ms

                FROM

                [logdata\QLog2.csv]

                (txt, codepage is 1254, embedded labels, delimiter is ';')

                Where Status = 0;

                 

                Mapped:

                Mapping LOAD

                ClearAlarmsTable.Key,

                ClearAlarmsTable.Time_ms

                Resident ClearAlarmsTable;

                 

                FinalTable:

                LOAD

                ReceivedAlarmsTable.Key,

                ReceivedAlarmsTable.Line,

                ReceivedAlarmsTable.MsgNumber,

                ReceivedAlarmsTable.MsgText,

                ReceivedAlarmsTable.Time_ms as ReceivedTime,

                ApplyMap('Mapped', ReceivedAlarmsTable.Key, '-') as ClearTime

                Resident ReceivedAlarmsTable;

                //

                 

                This is the result.

                 

                KeyLineMsgNumberMsgTextReceivedTimeClearTime
                1515Message510:0510:10
                1515Message510:2410:10
                1616Message610:22-
                112112Message1210:2010:30
                2525Message510:25-

                 

                ApplyMap function returns first mapped value. I must to map first nearest value of each ReceivedTime. Conditional Applymap or something that i need. Any suggestion? Thank you.