topic Re: NUMBER in QlikView

NUMBER

upaliwije — Mon, 16 Nov 2015 08:40:01 GMT

I have following excel table

POLICY NO SEQNO

BR00101F003248	1
BR00111F0003387	1
BR00111F0003398	2
BR00111F0003637	2
BR00111F0003860	1
BR00111F0003955	1
BR00111F0003970	1
BR00121F0004002	4

WHEN I LOAD ABOVE IN THE FOLLOWING WAY

join(claim)

LOAD POLICY_NO,

SEQNO

FROM...

After loading when I see the result in my pivot table in some cases I get - mark instead of SEQNO under SEQNO coloumn

Pls help me to correct it

Re: NUMBER

petter — Mon, 16 Nov 2015 08:44:54 GMT

If a POLICY_NO exists in the claim table but not in your Excel table then you will get a null value which is displayed as a dash - mark

You could use a MAPPING LOAD with a mapping table and use the ApplyMap instead. This gives you the option of putting in a default value if there is no match.

Re: NUMBER

jagan — Mon, 16 Nov 2015 09:07:10 GMT

Hi,

I think some Policy Numbers are missing in the file which are there in the claims table, try to fix that, or otherwise use Mapping Load & ApplyMap() for mapping and set your default value if POLICY_NO is missed.

Method1:

Policy:

Mapping LOAD

POLICY_NO,

SEQNO

FROM Policy;

claim:

LOAD

ApplyMap('Policy', POLICY_NO, 'N/A') AS SEQNO;

Method2:

claimtemp:

LOAD

FROM Claim;

LEFT JOIN (claimtemp)

LOAD

POLICY_NO,

SEQNO

FROM Policy;

claim:

Noconcatenate

LOAD

If(Len(Trim(SEQNO)) = 0, 'N/A', SEQNO

RESDIENT claimtemp;

DROP TABLE claimtemp;

Note: among the 2 methods Mapping Load is the best method.

Hope this helps you.

Regards,

jagan.

Re: NUMBER

upaliwije — Mon, 16 Nov 2015 09:07:20 GMT

Thanks

In fact I get - value even if matching records exits. I wonder how some records are correctly displayed while others are not

Re: NUMBER

Colin-Albert — Mon, 16 Nov 2015 09:22:48 GMT

What expression are you using for SEQNO in your pivot table?

If you have just used SEQNO with no aggregation expression, then QlikView will assume only(SEQNO).

Only() will display null unless the row only contains one value.

Try using max(SEQNO) or maxstring(SEQNO).

Chart expression should always use an aggregation such as sum().