NUMBER

[upaliwije]

Hi

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

[petter]

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.

[jagan]

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.

[upaliwije]

Thanks

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

[Colin-Albert]

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().