Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
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

Re: logic scripting...

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

Re: logic scripting...

So you want to exclude ID 2 here?

Re: logic scripting...

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

Re: logic scripting...

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

Re: logic scripting...

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;

Re: logic scripting...

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

Re: logic scripting...

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

stalwar1

It is pulling in all the Dates.

Re: logic scripting...

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

Re: logic scripting...

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.

Re: logic scripting...

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