logic scripting...

[#1]

I have some data as below....

ID      SubID      ExpDate

1         ABC        8/9/16

1         CDE        8/9/16

2         FGH        7/9/16

2         IJK          8/9/16

Here I want to exclude the subspace ID's where expiry dates are different.How can I do this in the script level...

[sunny_talwar]

So you want to exclude ID 2 here?

[sunny_talwar]

May be this:

Table:

LOAD * INLINE [

ID,      SubID,      ExpDate

1,         ABC,        8/9/16

1,         CDE,        8/9/16

2,         FGH,        7/9/16

2,         IJK,          8/9/16

];

Right Join (Table)

LOAD ID

Where Count = 1;

LOAD ID,

          Count(DISTINCT ExpDate) as Count

Resident Table

Group By ID;

[#4]

Sorry.I was not clear maybe.

I want to exclude SubID FGH as ID 2 has 2 SubID's with different Exp dates...but based on the ExpDate want to include the max( ExpDate.)

So output should be like below...

ID,      SubID,      ExpDate

1,         ABC,        8/9/16

1,         CDE,        8/9/16

2,         IJK,          8/9/16

[sunny_talwar]

So you want to keep all record from the max date?

Table:

LOAD * INLINE [

ID,      SubID,      ExpDate

1,         ABC,        8/9/16

1,         CDE,        8/9/16

2,         FGH,        7/9/16

2,         IJK,          8/9/16

];

Right Join (Table)

LOAD Max(ExpDate) as ExpDate

Resident Table;

[sunny_talwar]

Or may be this:

Table:

LOAD * INLINE [

ID,      SubID,      ExpDate

1,         ABC,        8/9/16

1,         CDE,        8/9/16

2,         FGH,        7/9/16

2,         IJK,          8/9/16

];

Right Join (Table)

LOAD SubID,

           Max(ExpDate) as ExpDate

Resident Table

Group By SubID;

[#7]

Not sure if it is working..PFA attached QVW.

stalwar1‌

It is pulling in all the Dates.

[sunny_talwar]

My bad, you need this:

Table:

LOAD * INLINE [

ID,      SubID,      ExpDate

1,         ABC,        8/9/16

1,         CDE,        8/9/16

2,         FGH,        7/9/16

2,         IJK,        8/9/16

];

Right Join (Table)

LOAD ID,

     Max(ExpDate) as ExpDate

Resident Table

Group By ID;

[#9]

stalwar1‌ sunny PFA QVW and Excel.

The logic is working fine when I do the Inline load but when I load from Excel it is not working.Not sure what wrong I am doing.

Thanks much for your help.

[sunny_talwar · Accepted solution]

Check this:

Table:

LOAD

    ID,

    SubID,

    SubExpire

FROM

[subtest.xlsx]

(ooxml, embedded labels, table is Sheet1)

WHERE NOT([SubExpire] < today()) ;

Right Join (Table)

LOAD ID,

    Max(Date([SubExpire])) as SubExpire

Resident Table

Group By ID;