10 Replies Latest reply: Oct 1, 2014 7:40 AM by Herbert Chitate RSS

    Unique No Load

      Afternoon All,

       

      I have the attached dummy data as my source data. What would the script look like to load only the latest transaction date with the corresponding client ID.

       

       

      Thank you

       

      H

        • Re: Unique No Load
          Ashfaq Mohammed

          Like this

           

          Directory;

          LOAD max(date(date#([Transaction Date],'DD.MM.YYYY'))) as [Transaction Date],

               [Client ID]

          FROM

          [Unique No..xls]

          (biff, embedded labels, table is Sheet1$)

          Group by [Client ID];

           

          Regards

          ASHFAQ

            • Re: Unique No Load

              Thanks Ashfaq,

               

              My data has more columns, do I need to include all columns in the group by part?

               

              H

                • Re: Unique No Load
                  Ashfaq Mohammed

                  Try like this if you don't want to group by.

                  It depends on your data granularity.

                   

                   

                   

                   

                  Directory;

                  LOAD

                  only(Field1) as Field1,

                  only(Field2) as Field2,

                  max(date(date#([Transaction Date],'DD.MM.YYYY'))) as [Transaction Date],

                       [Client ID]

                  FROM

                  [Unique No..xls]

                  (biff, embedded labels, table is Sheet1$)

                  Group by [Client ID];

                   

                  Regards

                  ASHFAQ

                    • Re: Unique No Load

                      One last one Ashfaq,

                       

                      Why am I getting an invalid expression error when I run the following code:

                       

                      LOAD*,

                      max(date(date#([Received Date],'DD.MM.YYYY'))) as [NReceived Date]

                      Group by NTIMB;

                      LOAD

                      [Received Date],

                      if(TIMB = 0 or TIMB = 1 or TIMB = 7 or TIMB = 600,[First Name]& Surname, TIMB) as NTIMB

                      FROM

                      [..\Survey Reports\Forestry\Forestry - Nursery.xlsx]

                      (ooxml, embedded labels, table is [1 Forestry Nursery])

                      ;

                       

                      Thanks

                        • Re: Unique No Load
                          Ashfaq Mohammed

                          Hi,

                           

                          Try like this

                           

                          LOAD NTIMB,

                          max(date(date#([Received Date],'DD.MM.YYYY'))) as [NReceived Date]

                          Group by NTIMB;

                          LOAD

                          [Received Date],

                          if(TIMB = 0 or TIMB = 1 or TIMB = 7 or TIMB = 600,[First Name]& Surname, TIMB) as NTIMB

                          FROM

                          [..\Survey Reports\Forestry\Forestry - Nursery.xlsx]

                          (ooxml, embedded labels, table is [1 Forestry Nursery])

                           

                          Regards

                          ASHFAQ

                            • Re: Unique No Load

                              Great Answer Ashfaq,

                               

                              But why did the Load * refuse?

                               

                              Thanks

                               

                              H

                                • Re: Unique No Load
                                  Ashfaq Mohammed

                                  Load * Will load all fields

                                  by this you will have [NReceived Date] two time, which is not allowed.

                                   

                                  Regards

                                  ASHFAQ

                                    • Re: Unique No Load

                                      Hi Ashfaq,

                                       

                                      The aggregation is working in that it is now producing a unique ID for each individual, but the [NReceived Date] is giving a blank. Would you know why by any chance? The final script looks like this:

                                       

                                      [code]

                                      Nursery:

                                      Load Distinct NTIMB,

                                      max(date(date#([Received Date],'DD.MM.YYYY'))) as [NReceived Date],

                                      [NFieldworker Name],

                                      Ngps_conf,

                                      //Ngps_conf_Location_Longitude,

                                      //Ngps_conf_Location_Latitude,

                                      //Ngps_conf_Location_Altitude,

                                      //Ngps_conf_Location_Time,

                                      NSurname,

                                      [NFirst Name],

                                      [Float beds],

                                      [Reason_Incorrect Dimension],

                                      [Reason_Plastic Leaking],

                                      [Reason_No Shade],

                                      [Reason_No Protection],

                                      [Reason_Poor grow Mix],

                                           [Reason_Low Water],

                                           Trays,

                                           [Spec Trays],

                                          Seedlings,

                                           Height,

                                           [Pricking out],

                                           [P out Y],

                                           [P out N],

                                           Hardening,

                                           [Hard Y],

                                           [Hard N],

                                           Pest,

                                           [Pest_type_Cut Worm],

                                           Pest_type_Locusts,

                                           Disease,

                                           [Disease_Type_Damping Off],

                                           [Disease_Type_Leaf Disease],

                                           NManager,

                                           NArea,

                                           [NArea Manager]

                                          

                                           Group by    NTIMB,[NFieldworker Name],

                                        Ngps_conf,

                                      NSurname,

                                      [NFirst Name],

                                      NTIMB,

                                      [Float beds],

                                      [Reason_Incorrect Dimension],

                                      [Reason_Plastic Leaking],

                                      [Reason_No Shade],

                                      [Reason_No Protection],

                                      [Reason_Poor grow Mix],

                                           [Reason_Low Water],

                                           Trays,

                                           [Spec Trays],

                                          Seedlings,

                                           Height,

                                           [Pricking out],

                                           [P out Y],

                                           [P out N],

                                           Hardening,

                                           [Hard Y],

                                           [Hard N],

                                           Pest,

                                           [Pest_type_Cut Worm],

                                           Pest_type_Locusts,

                                           Disease,

                                           [Disease_Type_Damping Off],

                                           [Disease_Type_Leaf Disease],

                                           NManager,

                                           NArea,

                                           [NArea Manager];

                                          

                                      LOAD //[Submission Id],

                                           [Fieldworker Name] as [NFieldworker Name],

                                           //[Fieldworker Id],

                                           //[Repeats On Question],

                                           //[Repeat Question Value],

                                           //[Repeating Index],

                                           [Received Date],

                                           gps_conf as Ngps_conf,

                                           gps_conf_Location_Longitude as Ngps_conf_Location_Longitude,

                                           gps_conf_Location_Latitude as Ngps_conf_Location_Latitude,

                                           gps_conf_Location_Altitude as Ngps_conf_Location_Altitude,

                                           gps_conf_Location_Time as Ngps_conf_Location_Time,

                                           Surname as NSurname,

                                           [First Name] as [NFirst Name],

                                           if(TIMB = 0 or TIMB = 1 or TIMB = 7 or TIMB = 600,[First Name]& Surname, TIMB) as NTIMB,

                                           [Float beds],

                                           [Reason_Incorrect Dimension],

                                           [Reason_Plastic Leaking],

                                           [Reason_No Shade],

                                           [Reason_No Protection],

                                           [Reason_Poor grow Mix],

                                           [Reason_Low Water],

                                           Trays,

                                           [Spec Trays],

                                           If(Seedlings = 'Other',[Spec Seedlings],Seedlings) as Seedlings,

                                           Height,

                                           [Pricking out],

                                           [P out Y],

                                           [P out N],

                                           Hardening,

                                           [Hard Y],

                                           [Hard N],

                                           Pest,

                                           [Pest_type_Cut Worm],

                                           Pest_type_Locusts,

                                           Disease,

                                           [Disease_Type_Damping Off],

                                           [Disease_Type_Leaf Disease],

                                           ApplyMap('Field_Tech_Mapping_Manager',[Fieldworker Name],'Unknown') as NManager,

                                           ApplyMap('Field_Tech_Mapping_Area',[Fieldworker Name],'Unknown') as NArea,

                                           ApplyMap('Field_Tech_Mapping_AManager',[Fieldworker Name],'Unknown') as [NArea Manager]

                                        

                                      FROM

                                      [..\Survey Reports\Forestry\Forestry - Nursery.xlsx]

                                      (ooxml, embedded labels, table is [1 Forestry Nursery])

                                      ;

                                      [/code]

                                       

                                      Thanks

                                        • Re: Unique No Load
                                          Ashfaq Mohammed

                                          Hi,

                                           

                                          make sure you have proper format

                                           

                                          max(date(date#([Received Date],'DD.MM.YYYY'))) as [NReceived Date],


                                          If not change this to match the exact format.


                                          Regards

                                          ASHFAQ

                                            • Re: Unique No Load

                                              Hi Ashfaq,

                                               

                                              Almost there, I used "max(date([Received Date],'DD.MM.YYYY')) as [NReceived Date]" and this produces a date when I run the script. This NReceived Date column is actually a time stamp which will have date and time.

                                               

                                              I am getting duplicate records in other words for some clients I am getting back more than one transaction. If I export to excel and click on the date, I can actually see the time stamp in the cell, although the cell will be formatted to date only.

                                               

                                              I really wanted to end up with just one transaction per client being the latest transaction date hence the "max". How do I get rid of these duplicates?

                                               

                                              Thanks