Null value just wont change! Why??

[Nolgath]

I have a field 'dealer' and a field 'MatchStatus'.

In 'dealer' i have names (Strings) and i have multiple tables with dealer all with PrimaryKey. 

In 'MatchStatus' I have only 1 Value that is 'Existing' and nothing more, which I in a excel matched with the 'dealer's that I have existing in my system. Now in Qlik Sense I get 'Existing' for mine, and the rest I get 'Null' which is normal because I did not give any value attributed to them.

I am trying now to difference them, so I know what are new dealers and what are existing, I though it was pretty simple like:

=if(MatchStatus = Null(), 'New', 'Existing')

or

if(isnull(id), 'New', id)

or even 

if(len(trim(id))>0, id, 'New')

And I still get Null values, I don't understand why is not changing. Any ideias? Thank you!

[KGalloway]

It could be that there is an unintended join or association happening in your data. Could you provide some more detail about the data and its structure?

[Nolgath]

Hello, thank you for the reply!

Here is my script:

DealerMapping:
LOAD
id,
buyer_name,
dealerHash,
dealer,
MatchStatus,
Email,
"Contact Person"
FROM [lib://AttachedFiles/Dealers Mapping buyers and sellers.xlsx]
(ooxml, embedded labels, table is Sheet2);

Concatenate(DealerMapping)
LOAD Distinct
id,
buyer_name,
dealerHash,
dealer,
MatchStatus,
Email,
"Contact Person"
FROM [lib://AttachedFiles/Dealers Mapping buyers and sellers.xlsx]
(ooxml, embedded labels, table is Sheet2);

Concatenate(DealerMapping)
LOAD Distinct
dealerHash,
dealer;

[carDataExtended]:
SELECT
dealerHash,
dealer
FROM maria.carDataExtended;

I only have concatenated.

[KGalloway · Accepted solution]

I may be a little off with my assumptions about your data, but here's what I did:

1. I concatenated the tables like you did in the script.

2. I then loaded a new table from the existing DealerMapping and added the new field [DealerStatus] that identifies 'New' and 'Existing' based on the MatchStatus field.

DealerMapping:
LOAD * inline [
id,buyer_name,dealerHash,dealer,MatchStatus,Email,"Contact Person"
1, buyer1, dealerhash1, dealer1, Existing, email1, contact1
]
;

Concatenate(DealerMapping)
LOAD * inline [
id,buyer_name,dealerHash,dealer,MatchStatus,Email,"Contact Person"
2, buyer2, dealerhash2, dealer2, Existing, email2, contact2
]
;

Concatenate(DealerMapping)
LOAD * inline [
dealerHash,dealer
dealerhash3, dealer3
]
;

noconcatenate
final_table:
load
id,
buyer_name,
dealerHash,
dealer,
MatchStatus,
Email,
"Contact Person",
if(isnull(MatchStatus), 'New', 'Existing') as DealerStatus
resident DealerMapping;
drop table DealerMapping;

Alternatively, you could just add the field in the original loads:

DealerMapping:
LOAD * inline [
id,buyer_name,dealerHash,dealer,MatchStatus,Email,"Contact Person", DealerStatus
1, buyer1, dealerhash1, dealer1, Existing, email1, contact1, Existing
]
;

Concatenate(DealerMapping)
LOAD * inline [
id,buyer_name,dealerHash,dealer,MatchStatus,Email,"Contact Person", DealerStatus
2, buyer2, dealerhash2, dealer2, Existing, email2, contact2, Existing
]
;

Concatenate(DealerMapping)
LOAD * inline [
dealerHash,dealer, DealerStatus
dealerhash3, dealer3, New
]
;

Either way, I got this table:

Is this what you are looking for?