9 Replies Latest reply: Jan 20, 2016 3:07 AM by Steve Dark RSS

    Sorting table in script ?

    Ganesh Reddy

      Hi All,

       

      I have a senario where the temporary calender needs to be sorted in desending order of modified date, but i am unable to sort that table in script. Please find attached files.

      Note: As i got huge number of records i need to use distinct in that table.

       

      tab.PNG

       

      Best Regards,

      Ganesh.

        • Re: Sorting table in script ?
          Sunny Talwar

          May be this:

           

          Delta:

          LOAD id,

              site,

              partionkey,

              modifieddate as modifieddatetemp,

              sale

          FROM

          [Modi Test.txt]

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

           

          YEAR_MONTH_CALENDAR:

          LOAD Distinct

            timestamp(Monthstart(partionkey)) as MonthStart,

            timestamp(MonthEnd(partionkey)) as MonthEnd,

            Year(floor(partionkey))&NUM(Month(floor(partionkey)),'00') AS MonthName,

            modifieddatetemp as modifieddate

            Resident Delta

            Order by modifieddatetemp desc;

           

          FinalDelta:

          LOAD id,

            site,

            partionkey,

            modifieddatetemp as modifieddate,

            sale

          Resident Delta;

           

          DROP Table Delta;

           

          DataViewer is still incorrect sorting, but when you sort the data by load order in table box it shows the right sorting which means that the data is being correctly sorted:

           

          Capture.PNG

            • Re: Sorting table in script ?
              Ganesh Reddy

              Thanks for the reply sunny,

              I need to use this calender table further in script not in UI. Here we are getting DataViewer results only, because if i use peek function as mentioned below it is fetching the wrong record .

                                            peek('modifieddate',-1,'YEAR_MONTH_CALENDAR');

               

              My actual requirement is to sort months in desending order of modified date. Could you please help me out here.

               

              Ganesh.

                • Re: Sorting table in script ?
                  Steve Dark

                  Hi Ganesh,

                   

                  I notice you have a semi colon on the end of that statement, is this statement run on it's own after the load.

                   

                  If you do the sorted resident load, as recommended then this will give you the last modified date.

                   

                  More efficient would be to load the table in any order then do a resident LOAD with a GROUP BY and a MAX or a MIN.

                   

                  You only need to sort for a peek if you are referring back to the previous row in a load for example:

                   

                     ModifiedDate,

                     ModifiedDate - Peek('ModifiedDate', -1) as DaysSincePriorChange,

                   

                  Hope that helps,

                  Steve

              • Re: Sorting table in script ?
                Steve Dark

                Hi Ganesh,


                There are very few cases where sorting in the load script is necessary.  Using PEEK or doing an incremental load are the only cases I can think of.  In the main sorting in the front end makes much more sense.

                 

                Why do you think that sorting in the script is required?

                 

                There are some cases where the ORDER BY statement can not be used, loading from QVD for instance.  In these cases you can load to a temporary table and sort on the load from RESIDENT, as stalwar1 suggests.

                 

                The downside of this is it can make your load script take twice the time to execute.


                This DISTINCT predicate can put put in to any type of load, and will remove all duplicate rows - use with caution though as it can cause duplicate values to go missing - giving incorrect totals.  A GROUP BY with SUM statements is usually preferable.

                 

                Hope that helps.


                Steve

                  • Re: Sorting table in script ?
                    Ganesh Reddy

                    Hi Steve,

                     

                    Yes your assumption is correct, i am doing incrimental load with that calender table.

                     

                    Here sorting in the script is required , because it is a monthly QVD updation is there futher in script. Please find  metioned pseudo code.


                    Delta:

                    LOAD id,

                         site,

                         partionkey,

                         modifieddate,

                         sale

                    FROM

                    [Modi Test.txt]

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

                     

                     

                    YEAR_MONTH_CALENDAR:     

                      LOAD

                      distinct

                          timestamp(Monthstart(partionkey)) as MonthStart,

                      timestamp(MonthEnd(partionkey)) as MonthEnd,

                          Year(floor(partionkey))&NUM(Month(floor(partionkey)),'00') AS MonthName,

                          modifieddate

                         

                      Resident Delta Order by modifieddate desc;

                     

                       Let vCount=NoOfRows('YEAR_MONTH_CALENDAR');

                     

                     

                    /********************* Loop through each month generated in 'YEAR_MONTH_CALENDAR' table ***************************/

                     

                     

                      FOR i=1 to $(vCount)                      

                      LET vMonth = Peek('MonthName',-$(i),'YEAR_MONTH_CALENDAR');

                      LET vMonthStart = Peek('MonthStart',-$(i),'YEAR_MONTH_CALENDAR');

                      LET vMonthEnd = Peek('MonthEnd',-$(i),'YEAR_MONTH_CALENDAR');

                      LET vTableName =  'Result' & '$(vMonth)' ;

                      LET vReloadTimeIn = Now();

                     

                      /********** If QVD for YearMonth already exists Insert/Update to the existing QVD based on primary key, or else move to ELSE part to create new QVD for that month*******/

                     

                     

                      IF(not IsNull(FileTime('$(vQVDDataDir)\$(vTableName).qvd'))) then 

                     

                      [$(vTableName)]:

                      NoConcatenate

                      LOAD

                      *   

                      Resident Delta

                      Where Year(floor(PartitionKey))&NUM(Month(floor(PartitionKey)),'00')='$(vMonth)';

                        

                      CONCATENATE

                      LOAD

                      *

                      FROM [$(vQVDDataDir)\$(vTableName).qvd] (qvd)

                      WHERE NOT EXISTS (ID);  

                         ELSE                                

                     

                     

                         [$(vTableName)]:   

                         NoConcatenate

                      LOAD

                      *   

                         Resident Delta

                         Where Year(floor(PartitionKey))&NUM(Month(floor(PartitionKey)),'00')='$(vMonth)';

                     

                     

                      Drop table Delta;

                     

                     

                    In the above script i need to create a calender table with desending order of modified date which i am unable to do so far. Is there any other whay out to sort that table? Could you please help me out here.

                     

                     

                    Ganesh

                      • Re: Sorting table in script ?
                        Ganesh Reddy

                        Here the reason for soring with modified date is. Some times data base is updating any of previous months records only. It is a rare case as incrimental load with montly QVDs.

                         

                        Ganesh

                          • Re: Sorting table in script ?
                            Steve Dark

                            Hi Ganesh,

                             

                            I get the importance of the sorting now, the WHERE EXISTS wanting to see new records first.

                             

                            The problem you have is that the date is loading from the TXT file, so is inherently not a date, but is a text string.  When you sort (and the syntax for ORDER BY is correct in the script above) it is sorting as a string, not as a date.

                             

                            To solve this you will need to convert the date on load, like this:

                             

                            Delta:

                            LOAD id,

                                site,

                                partionkey,

                                Date#(modifieddate, 'YYYY-MM-DD hh:mm:ss') as modifieddate,

                                sale

                            FROM

                            [Modi Test.txt]

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

                             

                            Here it is critical that the date format string matches the format of the dates in the text file.  You can add a Date function around that code ( Date(Date#( ) to format the output - but if it is just used for sorting this is not important.

                             

                            I suspect that you also want to do similar to partionkey, if that is also a date.

                             

                            The easy way to test if that bit of code is working is to load the data from the text file in isolation and do a list box on modifieddate.  If it sorts right in a a ListBox then it will sort fine with an ORDER BY from a RESIDENT load.

                             

                            Hope that helps,

                            Steve