topic Re: logic scripting... in QlikView

logic scripting...

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: logic scripting...

sunny_talwar — Fri, 09 Sep 2016 12:49:54 GMT

So you want to exclude ID 2 here?

Re: logic scripting...

sunny_talwar — Fri, 09 Sep 2016 12:52:00 GMT

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;

Re: logic scripting...

Fri, 09 Sep 2016 13:04:00 GMT

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

sunny_talwar — Fri, 09 Sep 2016 13:07:08 GMT

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

sunny_talwar — Fri, 09 Sep 2016 13:08:56 GMT

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;

Re: logic scripting...

Fri, 09 Sep 2016 14:51:50 GMT

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

stalwar1‌

It is pulling in all the Dates.

Re: logic scripting...

sunny_talwar — Fri, 09 Sep 2016 15:02:34 GMT

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;

Re: logic scripting...

Tue, 13 Sep 2016 12:36:40 GMT

stalwar1‌ sunny 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...

sunny_talwar — Tue, 13 Sep 2016 12:46:24 GMT

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;

Re: logic scripting...

Tue, 13 Sep 2016 12:56:06 GMT

awesome.Thanks much as always.

Re: logic scripting...

Thu, 15 Sep 2016 14:32:09 GMT

stalwar1‌ I have a new logic that needs to be added.Can you please have a look.Attached are sample QVW and data file.

Thanks much.

Re: logic scripting...

Thu, 15 Sep 2016 15:34:20 GMT

stalwar1‌
sunny‌

Sunny...any help is highly appreciated.Thank you Kindly.

Re: logic scripting...

sunny_talwar — Thu, 15 Sep 2016 18:20:55 GMT

I responded to your new thread. I wasn't available when you were looking for a responses here.