Logic help in script level please.

[#1]

I have attached the sample QVW and  data.I need help in the logic.

If sub id has a renewal or renewed reason and multiple exp dates then pick max of exp date else include all sub id's.

Any help is much appreciated.

Thanks much.

[sunny_talwar · Accepted solution]

May be this?

Table:

LOAD ID,

    SubID,

    SubExpire,

    [Reason For Sub]

FROM

[subtest (1).xlsx]

(ooxml, embedded labels, table is Sheet1)

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

Left Join (Table)

LOAD ID,

  If(WildMatch(Concat([Reason For Sub], ','), '*Rene*'), 1, 0) as Flag

Resident Table

Group By ID; 

FinalTable:

NoConcatenate

LOAD *

Resident Table;

Right Join (FinalTable)

LOAD ID,

    Max(Date([SubExpire])) as SubExpire

Resident FinalTable

Where Flag = 1

Group By ID;

Concatenate(FinalTable)

LOAD ID,

    SubID,

    SubExpire,

    [Reason For Sub]

Resident Table

Where Flag = 0;

DROP Table Table;

[vishsaggi]

Can you clarify here, you want only to consider ID which has Reason for Sub as Renewal or Renewed. Coz  ID 400 has not got Renewal or Renewed from your excel sheet.

[#4]

you are wonderful