topic calculation dimension in QlikView

calculation dimension

buzzy996 — Wed, 26 Nov 2014 17:52:13 GMT

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

Re: calculation dimension

kushalthakral — Wed, 26 Nov 2014 18:27:10 GMT

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:

Re: calculation dimension

its_anandrjs — Wed, 26 Nov 2014 20:36:54 GMT

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

Re: calculation dimension

MarcoWedel — Wed, 26 Nov 2014 23:32:43 GMT

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

Re: calculation dimension

buzzy996 — Thu, 27 Nov 2014 09:40:55 GMT

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

Re: calculation dimension

buzzy996 — Thu, 27 Nov 2014 15:04:43 GMT

any help!