Unique No Load

[#1]

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

[ashfaq_haseeb]

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

[#3]

Thanks Ashfaq,

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

H

[ashfaq_haseeb]

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

[#5]

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

[ashfaq_haseeb · Accepted solution]

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

[#7]

Great Answer Ashfaq,

But why did the Load * refuse?

Thanks

H

[ashfaq_haseeb]

Load * Will load all fields

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

Regards

ASHFAQ

[#9]

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

[ashfaq_haseeb]

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