calculation dimension

[buzzy996]

hi,

I'm having couple of fields with the flowing 2 fields in straight table,

ID     Client   Doc     Date   

101      a         p         20/03/2014

101      a         p          -

102      b         n          -

103      c         p          -

I have to remove only 101 with date as null,how to do?

Note: I don't like to use suppress when values is null  this case on date field and Client & Doc data will change always.

any help!

thanks

[kushalthakral]

Dear Shiva

i have tried this with inline load and working fine, you can try it within ur script.

test:

LOAD * INLINE [

    ID, Client, Doc, Date

    101, a, p, 20-03-2014

    101, a, p

    102, b, n

    103, c, p

]

;

test1:

NoConcatenate

Load Client,Doc, Date,ID,

  if (previous (ID)<> ID, ID) as new_id

resident test where not isnull(Date) and not isnull(ID) order by  ID DESC, Date asc;

drop table test;

NoConcatenate

test2:

Load * Resident test1 where not IsNull(new_id);

drop table test1;

Please find the screenshot below of the output:

[its_anandrjs]

Hi Shiva,

Try to load your table like this way

Tab1:
LOAD * INLINE [
    ID, Client, Doc, Date
    101, a, p, 20-03-2014
    101, a, p
    102, b, n
    103, c, p
];

Tab2:
NoConcatenate
Load Client,Doc, Date,ID,
if(Len(Date) <= 0 and Previous(ID) = ID, 0,1) as NullFlag
Resident Tab1;
Drop table Tab1;

NoConcatenate
Final:
Load * Resident Tab2 where NullFlag=1;
Drop table Tab2;

Regards

Anand

[MarcoWedel]

try to load ordered by date using where clause "where not exists(ID)".

[buzzy996]

Thanks every one,

Actually the above two fields(ID & Date) are coming based on different scripts & different tables,so I don't want to modify my existing logic on code level.

so any suggestions to do from front end/straight table level by using some exp/cal dimensions?

thanks

[buzzy996]

any help!