topic Re: Unique No Load in QlikView

Unique No Load

Tue, 30 Sep 2014 13:23:59 GMT

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

Re: Unique No Load

ashfaq_haseeb — Tue, 30 Sep 2014 13:29:29 GMT

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

Tue, 30 Sep 2014 13:58:32 GMT

Thanks Ashfaq,

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

Re: Unique No Load

ashfaq_haseeb — Tue, 30 Sep 2014 14:00:52 GMT

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

Tue, 30 Sep 2014 14:54:03 GMT

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_haseeb — Tue, 30 Sep 2014 17:51:00 GMT

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

Wed, 01 Oct 2014 10:21:13 GMT

Great Answer Ashfaq,

But why did the Load * refuse?

Thanks

Re: Unique No Load

ashfaq_haseeb — Wed, 01 Oct 2014 10:24:54 GMT

Load * Will load all fields

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

Regards

ASHFAQ

Re: Unique No Load

Wed, 01 Oct 2014 11:20:18 GMT

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_haseeb — Wed, 01 Oct 2014 11:28:36 GMT

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

Wed, 01 Oct 2014 11:40:54 GMT

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