6 Replies Latest reply: Oct 3, 2016 8:57 AM by Ibrahim Düzenli RSS

    Latest date <= Today() from two SAP tables A004 and A005, Qlik Sense

    Ibrahim Düzenli

      Hello Qlik Community,

       

      I am loading two SAP tables in Qlik Sense. The name of the tables are A004 and A005. Both tables have the same columns. I search for a solution for my problem. I would like to get only one row with the latest date for each MATNR!

       

      Table NameKNUMH (ID)MATNRDATAB (Date from)
      A0042779C1K901512/1/2015
      A0041920C1K90151/1/2016
      A0041921C1K90153/18/2016
      A0041922C1K90009/1/2014

       

       

      Table NameKNUMH (ID)MATNRDATAB (Date from)
      A0052379C1K901512/1/2015
      A0052389C1K800112/12/2020
      A0051923C1K90019/29/2016

       

      Would like to get the current date, which is Current date >= DATAB (Date from):

      • Latest date in the table, which is not bigger then Today(9/29/2016). (e.g. KNUMH(ID) 1921,1922 or 1923)

       

      Tried different concepts using grouping and while loop, but couldn't get it to work. I saw a discussion, which is helpful but not the right way for my problem. Re: Load oldest date from two tables

       

      Desired result:

      Table NameKNUMH (ID)MATNRDATAB (Date from)
      A0041921C1K90153/18/2016
      A0041922C1K90009/1/2014
      A0051923C1K90019/29/2016

       

      Thank you very much

        • Re: Latest date <= Today() from two SAP tables A004 and A005, Qlik Sense
          Johan Lindell

          Hi,

           

          This should do the trick:

           

          SET DateFormat='MM/DD/YYYY';

           

           

          tmp:

          LOAD * Inline [

          Table Name; KNUMH (ID); MATNR; DATAB (Date from)

          A004; 2779; C1K9015; 12/1/2015

          A004; 1920; C1K9015; 1/1/2016

          A004; 1921; C1K9015; 3/18/2016

          A004; 1922; C1K9000; 9/1/2014] (delimiter is ';');

           

           

          Concatenate (tmp)

          LOAD * Inline [

          Table Name; KNUMH (ID); MATNR; DATAB (Date from)

          A005; 2379; C1K9015; 12/1/2015

          A005; 2389; C1K8001; 12/12/2020

          A005; 1923; C1K9001; 9/29/2016] (delimiter is ';');

           

           

          Table:

          LOAD MATNR,

            Max ([DATAB (Date from)]) as [DATAB (Date from)]

          Resident tmp

          Where [DATAB (Date from)] <= Today ()

          Group By MATNR;

           

           

          Left Join (Table)

          LOAD *

          Resident tmp;

           

           

          DROP Table tmp;

              • Re: Latest date <= Today() from two SAP tables A004 and A005, Qlik Sense
                Johan Lindell

                HI Ibrahim,

                 

                I realized that you could get duplicates with the above script if you were to have two records of a MATNR with the same DATAB. To keep only one record you can add the following to the code.

                 

                Brgds, Johan

                 

                 

                 

                RENAME Table Table to Table2;

                 

                 

                Table:

                NoConcatenate

                LOAD *,

                  If (MATNR = Peek (MATNR)

                     and [DATAB (Date from)] = Peek ([DATAB (Date from)]),

                  'Delete',

                  'Keep') as [Keep / Delete]

                Resident Table2

                Order By MATNR, [DATAB (Date from)];

                 

                 

                DROP Table Table2;

                 

                 

                Inner Join (Table)

                LOAD 'Keep' as [Keep / Delete]

                AutoGenerate (1);

                 

                 

                DROP Field [Keep / Delete];

                  • Re: Latest date <= Today() from two SAP tables A004 and A005, Qlik Sense
                    Ibrahim Düzenli

                    Hi johanlindell,

                     

                    thank you very much for your help. This code helps me a lot.

                     

                    You are right I have two records of a MATNR with the same DATAB, but I would like to keep only the record, which contains in column Table = 'A005'

                     

                    I tried a few codes, but no results. Do you know how can I handle it?

                     

                    Best regards,

                    Ibrahim

                      • Re: Latest date <= Today() from two SAP tables A004 and A005, Qlik Sense
                        Johan Lindell

                        SET DateFormat='MM/DD/YYYY';

                         

                         

                        tmp:

                        LOAD * Inline [

                        Table Name; KNUMH (ID); MATNR; DATAB (Date from)

                        A004; 2779; C1K9015; 12/1/2015

                        A004; 1920; C1K9015; 1/1/2016

                        A004; 1921; C1K9015; 3/18/2016

                        A004; 1922; C1K9000; 9/1/2014

                        A004; 1000; C1KXXXX; 9/1/2014] (delimiter is ';');

                         

                         

                        Concatenate (tmp)

                        LOAD * Inline [

                        Table Name; KNUMH (ID); MATNR; DATAB (Date from)

                        A005; 2379; C1K9015; 12/1/2015

                        A005; 2389; C1K8001; 12/12/2020

                        A005; 1923; C1K9001; 9/29/2016

                        A005; 1000; C1KXXXX; 9/1/2014] (delimiter is ';');

                         

                         

                        Table:

                        LOAD MATNR,

                          Max ([DATAB (Date from)]) as [DATAB (Date from)]

                        Resident tmp

                        Where [DATAB (Date from)] <= Today ()

                        Group By MATNR;

                         

                         

                        Left Join (Table)

                        LOAD *,

                          Num (Mid ([Table Name], 2, 3)) as [Table num]

                        Resident tmp;

                         

                         

                        DROP Table tmp;

                         

                         

                        RENAME Table Table to Table2;

                         

                         

                        Table:

                        NoConcatenate

                        LOAD *,

                          If (MATNR = Peek (MATNR)

                            and [DATAB (Date from)] = Peek ([DATAB (Date from)]),

                          'Delete',

                          'Keep') as [Keep / Delete]

                        Resident Table2

                        Order By MATNR, [DATAB (Date from)], [Table num] desc;

                         

                         

                        DROP Table Table2;

                         

                         

                        Inner Join (Table)

                        LOAD 'Keep' as [Keep / Delete]

                        AutoGenerate (1);

                         

                         

                        DROP Field [Keep / Delete];

                         

                        /******************************

                        I just made a number out of the table name to sort the records.

                        If you have more tables and need to sort them you can always

                        make an inline table with the table names and a sort number and

                        join that to the tmp-table before you select the records you want. !

                         

                        // Regards, Johan

                        ******************************/

                          • Re: Latest date <= Today() from two SAP tables A004 and A005, Qlik Sense
                            Ibrahim Düzenli

                            Hello johanlindell

                             

                            thank you very much for your help. I found another solution for my problem, but the first rows of code are your idea. I tried this code in Qlik Sense it works.

                             

                            I got the following table:

                            Table NameKNUMH (ID)MATNRDATAB (Date from)
                            A0051000C1KXXXX9/1/2014
                            A0041921C1K90153/18/2016
                            A0041922C1K90009/1/2014
                            A0051923C1K90019/29/2016

                             

                            Thank you again.

                             

                            Best regards,

                            Ibrahim

                             

                            SET DateFormat='MM/DD/YYYY';

                            A004:
                            LOAD * Inline [
                            Table Name; KNUMH (ID); MATNR; DATAB (Date from)
                            A004; 2779; C1K9015; 12/1/2015
                            A004; 1920; C1K9015; 1/1/2017
                            A004; 1921; C1K9015; 3/18/2016
                            A004; 1922; C1K9000; 9/1/2014
                            A004; 1000; C1KXXXX; 9/1/2014] (delimiter is ';');

                            A005:
                            Concatenate (A004)
                            LOAD * Inline [
                            Table Name; KNUMH (ID); MATNR; DATAB (Date from)
                            A005; 2379; C1K9015; 12/1/2015
                            A005; 2389; C1K8001; 12/12/2020
                            A005; 1923; C1K9001; 9/29/2016
                            A005; 1000; C1KXXXX; 9/1/2014] (delimiter is ';');

                            PriceTable:
                            LOAD MATNR,
                              Max ([DATAB (Date from)]) as [DATAB (Date from)]
                            Resident A004
                            Where [DATAB (Date from)] <= Today ()
                            Group By MATNR;

                            Left Join (PriceTable)
                            LOAD *
                            Resident A004;

                            DROP Table A004;

                            [WithTableFlag]:
                            LOAD
                              IF ([Table Name]='A004',1,0) AS [TableFlag],
                              *
                            RESIDENT [PriceTable];

                            [WithoutTableFlag]:
                            LOAD
                              *
                            WHERE NOT ([MATNR]=[Previous.MATNR] AND [Table Name]='A004');

                            LOAD
                              PREVIOUS ([MATNR]) AS [Previous.MATNR],
                              *
                            RESIDENT [WithTableFlag]
                            ORDER BY [MATNR],[TableFlag];


                            DROP TABLE [PriceTable];
                            DROP TABLE [WithTableFlag];

                             

                            RENAME TABLE [WithoutTableFlag] TO [PriceTable];