Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
Thanks
In fact I get - value even if matching records exits. I wonder how some records are correctly displayed while others are not
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().