Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

logic scripting...

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...

1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

13 Replies
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;

Not applicable
Author

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;

Not applicable
Author

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;

Not applicable
Author

stalwar1sunny 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

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;