8 Replies Latest reply: Dec 27, 2016 8:42 AM by Ganesh S RSS

    Creating Groups of data

    jacob meredith

      I have some data that looks like this:

       

       

       

      "MID" is a component. "Duty Cycle", "Service Condition", and "Title" are characteristics that describe each "MID". Questions "A", "B", and "C" are asked for each "MID", and each question has an answer.

       

      What I need to do is group all the "MID"s that have the same "Duty Cycle", "Service Condition", "Title", and "Answers" so that I can call upon those groups to see things like how many "MID"s are in each group. In the example above, I highlighted the "MID"s that would be grouped together in this example. 1 would group with 2, 3 would group with 5, and 4, 6, and 7 would be in their own groups.

       

      I was thinking the best way to do this was write a code that would create a new column and assign numbers to each "MID", and wherever the code came across a set of "Answer"s, "Duty Cycle"s, and "Service Condition"s that have already been read, it would assign the same number to that "MID"... Can somebody show me how to do what I suggested, or show me a better way to get what I need?

       

      Thanks.

        • Re: Creating Groups of data
          Gysbert Wassenaar

          Something like this:

           

          LOAD

               MID,

               Question,

               Answer,

               [Duty Cycle],

               [Service Condition],

               Title,

               AutoNumber( MID & '|' & Answer & '|' & [Duty Cycle]  & '|' & [Service Condition] & '|' &  Title) as GroupID

          FROM

               ...

               ;

            • Re: Creating Groups of data
              jacob meredith

              I think this will help, but there are two things that might be a problem.

               

              First, I need the groups to be dependent on the "Answer"s to the "Question"s also, not just "Duty Cycle", "Service Condition", and "Title".

               

              Second, I am using a SQL load to pull this data from a database, and these fields are not all from the same table. "MID", "Duty Cycle", and "Service Condition" are from "Table 1". "Question" and "Answer" are from "Table 2". "Title" is from "Table 3".

               

              Would it be best to concatenate the "Question" and "Answer" fields so I can include it in my autonumber function? If so, where should the concatenate function and the autonumber function go with respect to the SQL load?

                • Re: Creating Groups of data
                  Gysbert Wassenaar

                  All the questions from different MIDS should have the same answers then? Perhaps like this then:

                   

                  Data:

                  LOAD

                      MID,

                      Question,

                      Answer,

                      [Duty Cycle],

                      [Service Condition],

                      Title   

                  FROM

                      ...

                      ;

                   

                  JOIN ( Data)

                   

                  LOAD

                      MID,

                      [Duty Cycle],

                      [Service Condition],

                      Title,

                      AutoNumber( MID & '|' & Concat(Answer,'|', Answer)  & '|' & [Duty Cycle]  & '|' & [Service Condition] & '|' &  Title) as GroupID

                  RESIDENT

                      Data

                  GROUP BY

                      MID,

                      [Duty Cycle],

                      [Service Condition],

                      Title

                      ;

                     

              • Re: Creating Groups of data
                Vineeth Pujari

                can you post sample data with each table in separate tabs! and a tab to show expected output!

                 

                It's easier to comprehend than to read and visualise.

                  • Re: Creating Groups of data
                    jacob meredith

                    The "New Identifier" column in the last sheet is how I want the data to be grouped.

                      • Re: Creating Groups of data
                        jacob meredith

                        I made some changes to my data when I created this post in hopes of making it simpler. I still need the "MID"s to be grouped with other "MID"s that have the same "Service Condition", "Duty Cycle", "Title", and "Answer"s to each "Question".

                          • Re: Creating Groups of data
                            Vineeth Pujari

                            Check the Attached

                             

                            243343.JPG

                             

                            Map_TemplateID:

                            Mapping LOAD

                            [Template ID],

                            Title

                                

                            FROM

                            [F:\CommunityHelp\learning how to concatenate.xlsx]

                            (ooxml, embedded labels, table is tbtemplates);

                             

                             

                            TEMP:

                            LOAD [Template ID],

                            Applymap('Map_TemplateID',[Template ID]) as Title,

                                 MSID,

                                 MID

                            FROM

                            [F:\CommunityHelp\learning how to concatenate.xlsx]

                            (ooxml, embedded labels, table is tbmstrategy);

                             

                             

                            Left JOIN(TEMP)

                             

                            LOAD MID,

                                 [Duty Cycle],

                                 [Service Condition]

                             

                             

                            FROM

                            [F:\CommunityHelp\learning how to concatenate.xlsx]

                            (ooxml, embedded labels, table is tbequipment);

                             

                             

                            Left JOIN(TEMP)

                             

                            LOAD Question_ID,

                                 MSID,

                                 Answer

                             

                             

                            FROM

                            [F:\CommunityHelp\learning how to concatenate.xlsx]

                            (ooxml, embedded labels, table is tbquestionanswers);

                             

                            NoConcatenate

                             

                             

                            FACT:

                            LOAD * ,

                            AutoNumber([Duty Cycle]  & '|' & [Service Condition] & '|' & Title) as GroupID

                            RESIDENT TEMP;


                            DROP TABLE TEMP;

                            • Re: Creating Groups of data
                              Ganesh S

                              You might try like this,

                               

                              After Joining all the tables of the sheets in one table:

                               

                              TEMP1:
                              LOAD *,
                              if(MID=Peek(MID),Peek(ConcatKEy)&Key1,Key1) as ConcatKEy;
                              LOAD *,
                              Question_ID&[Service Condition]&[Duty Cycle]&Title&Answer as Key1,
                              Resident Final
                              Order by MID;

                               

                              FINAL:

                               

                              LOAD *, AutoNumber(NEWKEY) as NEWGRP;
                              LOAD *,
                              if(MID=Peek(MID),Peek(NEWKEY),ConcatKEy) as NEWKEY
                              Resident FINAL1
                              Order by MID asc, ConcatKEy desc;

                              DROP Table TEMP1,FINAL1;

                               

                               

                              And finally the field NEWGRP contains the group No.s

                               

                              And also it was a good exercise.

                               

                               

                              Regards,

                              Ganesh