create a new filter field

[0li5a3a]

Hi there,

LOAD * INLINE [

    TicketID, Location, Work

    1, AAA, wo-1

    2, BBB,

    3, BBB, wo-2

    4, BBB,

    5, CCC,

    6, DDD, wo-3

    7, EE,

    8, EE,

    9, EE,

    10, FFF

    11, FFF

];

what logic I need to apply to create a new field to load only the location where I don't have a Work?

Regards,

C

[dapostolopoylos]

LOAD *

Where

len(Trim(Work))=0;

LOAD * INLINE [

    TicketID, Location, Work

    1, AAA, wo-1

    2, BBB,

    3, BBB, wo-2

    4, BBB,

    5, CCC,

    6, DDD, wo-3

    7, EE,

    8, EE,

    9, EE,

    10, FFF

    11, FFF

];

[0li5a3a]

no this don't work in my case

[dapostolopoylos]

If you want to be able to select those No Work records try this:

LOAD

     TicketID,

     Location,

     if(len(Trim(Work))=0,"No Work",Work) as Work;

LOAD * INLINE [

    TicketID, Location, Work

    1, AAA, wo-1

    2, BBB,

    3, BBB, wo-2

    4, BBB,

    5, CCC,

    6, DDD, wo-3

    7, EE,

    8, EE,

    9, EE,

    10, FFF

    11, FFF

];

[sunny_talwar · Accepted solution]

May be this

Table:

LOAD * INLINE [

    TicketID, Location, Work

    1, AAA, wo-1

    2, BBB

    3, BBB, wo-2

    4, BBB

    5, CCC

    6, DDD, wo-3

    7, EE

    8, EE

    9, EE

    10, FFF

    11, FFF

];

Right Join (Table)

LOAD Location

Where Count = 0;

LOAD Location,

Sum(If(Len(Trim(Work)) > 0, 1, 0)) as Count

Resident Table

Group By Location;

[0li5a3a]

Thank you Sunny!