[personal kdb+] How to get NULL if desired condition is not matching

Community Support
1

Hi,

Please take look at the below requirement and let me know the
solution…

table t,

PRDID SDATE DEPTID

1001 01-01-2010 E91
1001 01-20-2010 E91
1001 02-07-2010 E91
1001 01-10-2010 E01
1001 01-11-2010 E01
1001 02-01-2010 E20

unkey select first -2#PRDID,first -2#SDATE,first -2#DEPTID by
PRDID,DEPTID from t where SDATE <= “02-15-2010”

I’m getting:

PRDID DEPTID PRDID SDATE DEPTID

1001 E91 1001 01-20-2010 E91
1001 E01 1001 01-10-2010 E01
1001 E20 1001 02-01-2010 E20

But my requirment is to get rows like this:

PRDID DEPTID PRDID SDATE DEPTID

1001 E91 1001 01-20-2010 E91
1001 E01 1001 01-10-2010 E01

Thanks

–
Submitted via Google Groups

2

Hello,It’s not exactly clear what you’re trying to do. But it looks likeyou want a result with duplicate columns filtered on a date from t?If that’s indeed what you’re looking for, your example result seemsflawed, since the date you’re filtering on seems to match everythingin your example table (<= “02-15-2010”, By the way is SDATE a string?Not sure why the date is enclosed in quotes in the select statement,but I assume SDATE is a string in table t).In any case, here’s an example that should work given my assumptionsabove.q)t:(PRDID:6#1001;SDATE:(“01-01-2010”;“01-20-2010”;“02-07-2010”;“01-10-2010”;“01-11-2010”;“02-01-2010”);DEPTID:((3#E91),(2#E01),`E20))q)tPRDID SDATE DEPTID-------------------------1001 “01-01-2010” E911001 “01-20-2010” E911001 “02-07-2010” E911001 “01-10-2010” E011001 “01-11-2010” E011001 “02-01-2010” E20q)select PRDID,DEPTID,PRDID,SDATE,DEPTID from t where(“D”$SDATE)<=2010.01.15PRDID DEPTID PRDID1 SDATE DEPTID1----------------------------------------1001 E91 1001 “01-01-2010” E911001 E01 1001 “01-10-2010” E011001 E01 1001 “01-11-2010” E01On Apr 30, 3:58?am, Jyoti <jyoti…> wrote:> Hi,>> Please take look at the below requirement and let me know the> solution…>> table t,>> PRDID ? SDATE ? ? ? ? ? DEPTID> -------------------------------------> 1001 ? ?01-01-2010 ? ? ?E91> 1001 ? ?01-20-2010 ? ? ?E91> 1001 ? ?02-07-2010 ? ? ?E91> 1001 ? ?01-10-2010 ? ? ?E01> 1001 ? ?01-11-2010 ? ? ?E01> 1001 ? ?02-01-2010 ? ? ?E20>> unkey select first -2#PRDID,first -2#SDATE,first -2#DEPTID by> PRDID,DEPTID from t where SDATE <= “02-15-2010”>> I’m getting:>> PRDID ? DEPTID ?PRDID ? SDATE ? ? ? ? ? DEPTID> ------------------------------------------------> 1001 ? ?E91 ? ? 1001 ? ?01-20-2010 ? ? ?E91> 1001 ? ?E01 ? ? 1001 ? ?01-10-2010 ? ? ?E01> 1001 ? ?E20 ? ? 1001 ? ?02-01-2010 ? ? ?E20>> But my requirment is to get rows like this:>> PRDID ? DEPTID ?PRDID ? SDATE ? ? ? ? ? DEPTID> --------------------------------------------------------------------> 1001 ? ?E91 ? ? 1001 ? ?01-20-2010 ? ? ?E91> 1001 ? ?E01 ? ? 1001 ? ?01-10-2010 ? ? ?E01>> Thanks>> –>

Submitted via Google Groups</jyoti…>

3

Hi,

Thanks for the replay…

My requirement is:
I want 2nd last (last but one) values from the table group by PRDID,DEPTID and SDATE <= “02-15-2010”.

If there is only 1 record present in the table for the above condition then it should return NULL rows instead of the same row that is available in the table.

The table that I mentioned below I want an out put like this:

PRDID ? SDATE ? ? ? ? ? DEPTID

1001 ? ?01-20-2010 ? ? ?E91
1001 ? ?01-10-2010 ? ? ?E01

To meet my requirement I tried this query:
q> unkey select first -2#PRDID,first -2#SDATE,first -2#DEPTID by PRDID,DEPTID from t where SDATE <= “02-15-2010”

and the response that I got is:
PRDID ? SDATE ? ? ? ? ? DEPTID

1001 ? ?01-20-2010 ? ? ?E91
1001 ? ?01-10-2010 ? ? ?E01
1001 ? ?02-01-2010 ? ? ?E20

As per my requirement I don’t want the last row (highlighted) as that is not the 2nd last value (condition PRDID & DEPTID).

I think this time I’m more clear in explaining the requirement

Thanks,
Jyoti

4

select from (select last prev SDATE by PRDID,DEPTID from t) where notSDATE like ""On May 5, 2:37?am, Jyoti Sahoo <jyoti…> wrote:> Hi,>> Thanks for the replay…>> My requirement is:> I want 2nd last (last but one) values from the table group by PRDID,DEPTID> and SDATE <= “02-15-2010”.>> If there is only 1 record present in the table for the above condition then> it should return NULL rows instead of the same row that is available in the> table.>> The table that I mentioned below I want an out put like this:>> PRDID ? SDATE ? ? ? ? ? DEPTID> -------------------------------------------> ?1001 ? ?01-20-2010 ? ? ?E91> 1001 ? ?01-10-2010 ? ? ?E01>> To meet my requirement I tried this query:> q> unkey select first -2#PRDID,first -2#SDATE,first -2#DEPTID by> PRDID,DEPTID from t where SDATE <= “02-15-2010”>> and the response that I got is:> PRDID ? SDATE ? ? ? ? ? DEPTID> ------------------------------------------------> 1001 ? ?01-20-2010 ? ? ?E91> 1001 ? ?01-10-2010 ? ? ?E01> 1001 ? ?02-01-2010 ? ? ?E20>> As per my requirement I don’t want the last row (highlighted) as that is not> the 2nd last value (condition PRDID & DEPTID).>> I think this time I’m more clear in explaining the requirement>> Thanks,> Jyoti>> On Wed, May 5, 2010 at 6:13 AM, gabber <gabriel.rica…> wrote:> > Hello,> > It’s not exactly clear what you’re trying to do. ?But it looks like> > you want a result with duplicate columns filtered on a date from t?> > If that’s indeed what you’re looking for, your example result seems> > flawed, since the date you’re filtering on seems to match everything> > in your example table (<= “02-15-2010”, By the way is SDATE a string?> > Not sure why the date is enclosed in quotes in the select statement,> > but I assume SDATE is a string in table t).> > In any case, here’s an example that should work given my assumptions> > above.>> > q)t:(PRDID:6#1001;SDATE:>> > (“01-01-2010”;“01-20-2010”;“02-07-2010”;“01-10-2010”;“01-11-2010”;“02-01-20?10”);DEPTID:> > ((3#E91),(2#E01),`E20))> > q)t> > PRDID SDATE ? ? ? ?DEPTID> > -------------------------> > 1001 ?“01-01-2010” E91> > 1001 ?“01-20-2010” E91> > 1001 ?“02-07-2010” E91> > 1001 ?“01-10-2010” E01> > 1001 ?“01-11-2010” E01> > 1001 ?“02-01-2010” E20> > q)select PRDID,DEPTID,PRDID,SDATE,DEPTID from t where> > (“D”$SDATE)<=2010.01.15> > PRDID DEPTID PRDID1 SDATE ? ? ? ?DEPTID1> > ----------------------------------------> > 1001 ?E91 ? ?1001 ? “01-01-2010” E91> > 1001 ?E01 ? ?1001 ? “01-10-2010” E01> > 1001 ?E01 ? ?1001 ? “01-11-2010” E01>> > On Apr 30, 3:58 am, Jyoti <jyoti…> wrote:> > > Hi,>> > > Please take look at the below requirement and let me know the> > > solution…>> > > table t,>> > > PRDID ? SDATE ? ? ? ? ? DEPTID> > > -------------------------------------> > > 1001 ? ?01-01-2010 ? ? ?E91> > > 1001 ? ?01-20-2010 ? ? ?E91> > > 1001 ? ?02-07-2010 ? ? ?E91> > > 1001 ? ?01-10-2010 ? ? ?E01> > > 1001 ? ?01-11-2010 ? ? ?E01> > > 1001 ? ?02-01-2010 ? ? ?E20>> > > unkey select first -2#PRDID,first -2#SDATE,first -2#DEPTID by> > > PRDID,DEPTID from t where SDATE <= “02-15-2010”>> > > I’m getting:>> > > PRDID ? DEPTID ?PRDID ? SDATE ? ? ? ? ? DEPTID> > > ------------------------------------------------> > > 1001 ? ?E91 ? ? 1001 ? ?01-20-2010 ? ? ?E91> > > 1001 ? ?E01 ? ? 1001 ? ?01-10-2010 ? ? ?E01> > > 1001 ? ?E20 ? ? 1001 ? ?02-01-2010 ? ? ?E20>> > > But my requirment is to get rows like this:>> > > PRDID ? DEPTID ?PRDID ? SDATE ? ? ? ? ? DEPTID> > > --------------------------------------------------------------------> > > 1001 ? ?E91 ? ? 1001 ? ?01-20-2010 ? ? ?E91> > > 1001 ? ?E01 ? ? 1001 ? ?01-10-2010 ? ? ?E01>> > > Thanks>> > > –> > >

Submitted via Google Groups</jyoti…></gabriel.rica…></jyoti…>

5

Thanks … you made may day :-)

6

On May 5, 6:57?pm, Jyoti Sahoo <jyoti…> wrote:> Thanks … you made may day :-)>> On Wed, May 5, 2010 at 6:42 PM, agarwalpra...@gmail.com <>>>>>> agarwalpra...@gmail.com> wrote:> > select from (select last prev SDATE by PRDID,DEPTID from t) where not> > SDATE like “”>> > On May 5, 2:37 am, Jyoti Sahoo <jyoti…> wrote:> > > Hi,>> > > Thanks for the replay…>> > > My requirement is:> > > I want 2nd last (last but one) values from the table group by> > PRDID,DEPTID> > > and SDATE <= “02-15-2010”.>> > > If there is only 1 record present in the table for the above condition> > then> > > it should return NULL rows instead of the same row that is available in> > the> > > table.>> > > The table that I mentioned below I want an out put like this:>> > > PRDID ? SDATE ? ? ? ? ? DEPTID> > > -------------------------------------------> > > ?1001 ? ?01-20-2010 ? ? ?E91> > > 1001 ? ?01-10-2010 ? ? ?E01>> > > To meet my requirement I tried this query:> > > q> unkey select first -2#PRDID,first -2#SDATE,first -2#DEPTID by> > > PRDID,DEPTID from t where SDATE <= “02-15-2010”>> > > and the response that I got is:> > > PRDID ? SDATE ? ? ? ? ? DEPTID> > > ------------------------------------------------> > > 1001 ? ?01-20-2010 ? ? ?E91> > > 1001 ? ?01-10-2010 ? ? ?E01> > > 1001 ? ?02-01-2010 ? ? ?E20>> > > As per my requirement I don’t want the last row (highlighted) as that is> > not> > > the 2nd last value (condition PRDID & DEPTID).>> > > I think this time I’m more clear in explaining the requirement>> > > Thanks,> > > Jyoti>> > > On Wed, May 5, 2010 at 6:13 AM, gabber <gabriel.rica…>> > wrote:> > > > Hello,> > > > It’s not exactly clear what you’re trying to do. ?But it looks like> > > > you want a result with duplicate columns filtered on a date from t?> > > > If that’s indeed what you’re looking for, your example result seems> > > > flawed, since the date you’re filtering on seems to match everything> > > > in your example table (<= “02-15-2010”, By the way is SDATE a string?> > > > Not sure why the date is enclosed in quotes in the select statement,> > > > but I assume SDATE is a string in table t).> > > > In any case, here’s an example that should work given my assumptions> > > > above.>> > > > q)t:(PRDID:6#1001;SDATE:>> > (“01-01-2010”;“01-20-2010”;“02-07-2010”;“01-10-2010”;“01-11-2010”;“02-01-20??10”);DEPTID:> > > > ((3#E91),(2#E01),`E20))> > > > q)t> > > > PRDID SDATE ? ? ? ?DEPTID> > > > -------------------------> > > > 1001 ?“01-01-2010” E91> > > > 1001 ?“01-20-2010” E91> > > > 1001 ?“02-07-2010” E91> > > > 1001 ?“01-10-2010” E01> > > > 1001 ?“01-11-2010” E01> > > > 1001 ?“02-01-2010” E20> > > > q)select PRDID,DEPTID,PRDID,SDATE,DEPTID from t where> > > > (“D”$SDATE)<=2010.01.15> > > > PRDID DEPTID PRDID1 SDATE ? ? ? ?DEPTID1> > > > ----------------------------------------> > > > 1001 ?E91 ? ?1001 ? “01-01-2010” E91> > > > 1001 ?E01 ? ?1001 ? “01-10-2010” E01> > > > 1001 ?E01 ? ?1001 ? “01-11-2010” E01>> > > > On Apr 30, 3:58 am, Jyoti <jyoti…> wrote:> > > > > Hi,>> > > > > Please take look at the below requirement and let me know the> > > > > solution…>> > > > > table t,>> > > > > PRDID ? SDATE ? ? ? ? ? DEPTID> > > > > -------------------------------------> > > > > 1001 ? ?01-01-2010 ? ? ?E91> > > > > 1001 ? ?01-20-2010 ? ? ?E91> > > > > 1001 ? ?02-07-2010 ? ? ?E91> > > > > 1001 ? ?01-10-2010 ? ? ?E01> > > > > 1001 ? ?01-11-2010 ? ? ?E01> > > > > 1001 ? ?02-01-2010 ? ? ?E20>> > > > > unkey select first -2#PRDID,first -2#SDATE,first -2#DEPTID by> > > > > PRDID,DEPTID from t where SDATE <= “02-15-2010”>> > > > > I’m getting:>> > > > > PRDID ? DEPTID ?PRDID ? SDATE ? ? ? ? ? DEPTID> > > > > ------------------------------------------------> > > > > 1001 ? ?E91 ? ? 1001 ? ?01-20-2010 ? ? ?E91> > > > > 1001 ? ?E01 ? ? 1001 ? ?01-10-2010 ? ? ?E01> > > > > 1001 ? ?E20 ? ? 1001 ? ?02-01-2010 ? ? ?E20>> > > > > But my requirment is to get rows like this:>> > > > > PRDID ? DEPTID ?PRDID ? SDATE ? ? ? ? ? DEPTID> > > > > --------------------------------------------------------------------> > > > > 1001 ? ?E91 ? ? 1001 ? ?01-20-2010 ? ? ?E91> > > > > 1001 ? ?E01 ? ? 1001 ? ?01-10-2010 ? ? ?E01>> > > > > Thanks>> > > > > –> > > > > You received this message because you are subscribed to the Google> > Groups> > > > “Kdb+ Personal Developers” group.> > > > > To post to this group, send email to> > personal-kdbplus@googlegroups.com.> > > > > To unsubscribe from this group, send email to> > > > personal-kdbplus+unsubscribe@googlegroups.com> > > > > > .> > > > > For more options, visit this group athttp://> > > > groups.google.com/group/personal-kdbplus?hl=en.>> > > > –> > > > You received this message because you are subscribed to the Google> > Groups> > > > “Kdb+ Personal Developers” group.> > > > To post to this group, send email to personal-kdbplus@googlegroups.com> > .> > > > To unsubscribe from this group, send email to> > > > personal-kdbplus+unsubscribe@googlegroups.com> > > > > > .> > > > For more options, visit this group at> > > >http://groups.google.com/group/personal-kdbplus?hl=en.&gt;&gt; > > –> > > Thanks,> > > Jyoti!!!>> > > –> > >

Submitted via Google Groups</jyoti…></gabriel.rica…></jyoti…></jyoti…>

7

In my requirement I have to show all previous (last but one value as the last record is the base value) recommendations for a given company. In one case I have to show all recommendations from the start (get the last values <= the start date ) and in others we have show with previous value which is my current requirements (if there is only 1 value is present <= start date, then we have show null).?

Thanks,
Jyoti

8

I think the problem is that first -2# which is similar to 1# -2#
doubles the last item if it is only one. -1# -1_ would solve that
issue.

–
Submitted via Google Groups

9

Thanks… this also works fine… now I have to see the data accuracy and performance then will chose any one

Regards,
Jyoti