I think this one is tricky, can someone help please...

Fri, 08 Jul 2016 16:50:57 GMT

Hi everyone,

I have an issue with some data and I'm fairly sure it's a tricky one.

I have the following basic script;

GlobalData:
LOAD Value,
ValuationDate,
SchemeMemberKey
FROM
(qvd);

Currently when I load this and put the data into a straight table it shows me blanks where the member has no contribution within the month. I can't count these blanks though as they don't actually exist within the data so what I need to do is add a 0 value against each member where there's a month that they haven't contributed.

That will then allow me to complete a member count into different categories, including members that have a contribution at the start of the year but not the end etc.....

I hope that makes sense as this one has stumped me for at least 2 days!!!

Re: I think this one is tricky, can someone help please...

boorgura — Fri, 08 Jul 2016 17:01:26 GMT

Please try this to eliminate the blanks:

GlobalData:
LOAD Value,
ValuationDate,
SchemeMemberKey
FROM
(qvd)

where len(trim(Value)) > 0;

Or if you want replace blanks with 0's

GlobalData:
LOAD if(len(trim(Value)) = 0, 0, Value) as Value,
ValuationDate,
SchemeMemberKey
FROM
(qvd);

topic Re: I think this one is tricky, can someone help please... in QlikView

I think this one is tricky, can someone help please...

Re: I think this one is tricky, can someone help please...