Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
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

Re: Logic help in script level please.

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

Re: Logic help in script level please.

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

vishsaggi
Esteemed Contributor III

Re: Logic help in script level please.

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

Re: Logic help in script level please.

you are wonderful