12 Replies Latest reply: Aug 5, 2015 11:19 AM by Andrew Thomas RSS

    Grouping issue with file

    Andrew Thomas


      Hi

       

      I'm trying to group some records together. I have the following

       

       

      JESTPR:

      LOAD
      OBJNR As JestOBJNR,
      STAT As ProjectStatus

      FROM
      [D:\SourceDocuments\PRODQLIK\DATA\SAP DATA\JESTPR.QVD]
      (
      qvd)

       

      The field STAT has multiple records to each record in OBJNR

       

      STAT has a record called 'I0046' and if the record is equal to this then I want to call it 'OPEN' return it with the field OBJNR

      If the field is not equal to 'I0046' then I want to group together the records and call them @CLOSED and return it with the field OBJNR

       

       

       

        • Re: Grouping issue with file
          Friedrich Hofmann

          Hi,

           

          then just modify your LOAD with a new calculated field, like

           

          >> LOAD OBJNR, STAT, IF(STAT='I0046','OPEN', '@CLOSED') as Status ... <<

          HTH

            • Re: Grouping issue with file
              Andrew Thomas

              Thanks

               

              But the issue is that there are many different codes that will define CLOSED status and I want to group them together to display CLOSED against one OBJNR

               

              e,g

              So I need to group all of the projectStatus codes to show 1 record of CLOSED against OBJNR PR0000002

               

              JestOBJNRProjectStatusStatus
              PR00000002E0001@CLOSED
              PR00000002I0001@CLOSED
              PR00000002I0002@CLOSED
              PR00000002I0005@CLOSED
              PR00000002I0028@CLOSED
              PR00000002I0042@CLOSED
              PR00000002I0045@CLOSED
              PR00000002I0082@CLOSED
              PR00000002I0295@CLOSED
              PR00000002I0460@CLOSED
                • Re: Grouping issue with file
                  Jonathan Dienst

                  In the front end, create a table with JestOBJNR and Status as dimensions, and wherever metrics you require in the expressions. The two dimension values will only be displayed once.

                   

                  If there is no other data in your source table, then do this:

                   

                  JESTPR:

                  LOAD DISTINCT

                  OBJNR As JestOBJNR,

                  If(STAT = 'I0046', 'Open', 'Closed') As OpenState

                  FROM

                  [D:\SourceDocuments\PRODQLIK\DATA\SAP DATA\JESTPR.QVD]

                  (qvd);

                    • Re: Grouping issue with file
                      Andrew Thomas

                      Hi

                       

                      No that doesn't work

                       

                      I need to end up with an open or closed status against each OBJNR

                      if STAT = 'I0046' then the record should show Closed

                      However if the record isnt 'I0046' there can be many other codes that represent OPEN status and I need to group these together against 1 OBJNR


                        • Re: Grouping issue with file
                          Jonathan Dienst

                          Well that is exactly what that script does -  the DISTINCT will collapse all the Closed lines into a single line.

                            • Re: Grouping issue with file
                              Andrew Thomas

                              Yes understand but see the example below.

                               

                              I want to be able to show 1 JestOBJNR record that has an open or closed status

                               

                              Closed status will always be 'I0046' but there will be other status recorded against the OBJNR

                              if 'I0046@ isnt present then the status is closed but I only want one record

                               

                              THe DISTINCT logic worked to the point where it gave me 1 record for open and 1 for closed against the same OBJNR


                              JestOBJNRSTAT
                              PR00000001I0001
                              PR00000001I0042
                              PR00000002E0001
                              PR00000002I0001
                              PR00000002I0002
                              PR00000002I0005
                              PR00000002I0028
                              PR00000002I0042
                              PR00000002I0045
                              PR00000002I0082
                              PR00000002I0295
                              PR00000002I0460
                              PR00000003I0001
                              PR00000003I0002
                              PR00000003I0045
                              PR00000003I0118
                              PR00000004I0001
                              PR00000004I0002
                              PR00000004I0005
                              PR00000004I0045
                              PR00000004I0118
                              PR00000005I0001
                              PR00000005I0002
                              PR00000005I0005
                              PR00000005I0045
                              PR00000005I0118
                              PR00000006I0001
                              PR00000006I0002
                              PR00000006I0005
                              PR00000006I0045
                              PR00000006I0118
                              PR00000007I0001
                              PR00000007I0002
                              PR00000007I0045
                              PR00000007I0118
                              PR00000008E0001
                              PR00000008I0001
                              PR00000008I0002
                              PR00000008I0028
                              PR00000008I0045
                              PR00000009I0001
                              PR00000009I0042
                              PR00000010E0001
                              PR00000010I0001
                              PR00000010I0002
                              PR00000010I0005
                              PR00000010I0028
                              PR00000010I0042
                              PR00000010I0045
                              PR00000010I0082
                              PR00000010I0295
                              PR00000010I0460
                              PR00000011I0001
                              PR00000011I0002
                              PR00000011I0045
                              PR00000011I0118
                              PR00000012I0001
                              PR00000012I0002
                              PR00000012I0005
                              PR00000012I0045
                              PR00000012I0118
                              PR00000013I0001
                              PR00000013I0002
                              PR00000013I0005
                              PR00000013I0045
                              PR00000013I0118
                              PR00000014I0001
                              PR00000014I0002
                              PR00000014I0045
                              PR00000014I0118
                              PR00000015I0001
                              PR00000015I0002
                              PR00000015I0045
                              PR00000015I0118
                              PR00000016I0001
                              PR00000016I0002
                              PR00000016I0005
                              PR00000016I0028
                              PR00000016I0042
                              PR00000016I0082
                              PR00000016I0118
                              PR00000016I0295
                              PR00000017E0001
                              PR00000017I0001
                              PR00000017I0028
                              PR00000017I0118
                              PR00000018I0001
                              PR00000018I0002
                              PR00000018I0005
                              PR00000018I0042
                              PR00000018I0045
                              PR00000018I0067
                              PR00000018I0093
                              PR00000019E0001
                              PR00000019E0005
                              PR00000019I0001
                              PR00000019I0002
                              PR00000019I0005
                              PR00000019I0028
                              PR00000019I0042
                              PR00000019I0045
                              PR00000019I0046
                              PR00000019I0082
                              PR00000019I0295
                              PR00000019I0460
                                • Re: Grouping issue with file
                                  Jonathan Dienst

                                  Andrew Thomas wrote:

                                   

                                  THe DISTINCT logic worked to the point where it gave me 1 record for open and 1 for closed against the same OBJNR

                                  That's correct. You did not specify that you want suppress the open if there are any closes...

                                   

                                  This will prevent loading open if any close states exist.

                                   

                                  JESTPR:

                                  LOAD DISTINCT

                                  OBJNR As JestOBJNR,

                                  'Closed' As OpenState

                                  FROM [D:\SourceDocuments\PRODQLIK\DATA\SAP DATA\JESTPR.QVD] (qvd)

                                  WHERE STAT <> 'I0046';

                                   

                                  Concatenate

                                  LOAD DISTINCT

                                  OBJNR As JestOBJNR,

                                  'Open' As OpenState

                                  FROM [D:\SourceDocuments\PRODQLIK\DATA\SAP DATA\JESTPR.QVD] (qvd)

                                  WHERE STAT = 'I0046' AND Not(Exists(JESTPR));

                                  WHERE STAT = 'I0046' AND Not(Exists(OBJNR ));

                          • Re: Grouping issue with file
                            Sinan Ozdemir

                            It seems like you want to create per record per OBJNR per status:

                            Capture.PNG

                            If so, you can accomplish this either in the front end or in the load script with another preceding load statement:

                            Capture.PNG

                            • Re: Grouping issue with file
                              Sasidhar Parupudi

                              something like this?

                              JESTPR:

                               

                              LOAD

                              OBJNR As JestOBJNR,

                              STAT As ProjectStatus,

                              If(STAT = 'I0046', 'Open', 'Closed') As OpenState

                               

                              FROM

                              [D:\SourceDocuments\PRODQLIK\DATA\SAP DATA\JESTPR.QVD]

                              (qvd);

                               

                              NoConcatenate

                              JESTPR1:

                              LOAD OBJNR As JestOBJNR,Concat(ProjectStatus,',') as TotalProjectStatus,OpenState

                              Resident JESTPR

                              group by JestOBJNR,OpenState;

                               

                               

                              drop table JESTPR;

                          • Re: Grouping issue with file
                            Jonathan Dienst

                            Are those the only codes involved?

                             

                            JESTPR:

                            LOAD

                            OBJNR As JestOBJNR,

                            STAT As ProjectStatus,

                            If(STAT = 'I0046', 'Open', 'Closed') As OpenState

                            FROM

                            [D:\SourceDocuments\PRODQLIK\DATA\SAP DATA\JESTPR.QVD]

                            (qvd);

                             

                            If there are more codes to interpret, you might want to use a mapping table.