Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Logic help in script level please.

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.

1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

3 Replies
sunny_talwar

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
Champion III
Champion III

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.

Not applicable
Author

you are wonderful