Mapping/Handling Null or Empty Value

[Luigi_Qlik]

Hello all,

I'm fairly new to Qlik and I've encountered a problem I am unable to resolve. I'm hoping someone can point me in the right direction! I feel like I might be approaching the issue from the wrong direction.

Scenario: I'm loading two QVD files which link by "Name". However, some names do not exist on one of the QVDs resulting in "-" value under one of the fields. I'll give a visual example below. To add a small twist, I'm already using an IF statement on the field in question which will be shown below too - this is to purge unwanted characters.

Example Code:

Structure:

LOAD

Name,

Location,

Supplier,

FROM [file](qvd);

Employees:

IF(LEN(TRIM(PurgeChar(Dept,'-0123456789')))>1,TRIM(PurgeChar(Dept,'-0123456789'))) AS Department,

Name,

Manager

FROM [file](qvd);

Results:

'Supplier' lists the company but also any out-sourced companies. The 'Employees' QVD only lists staff employed directly whereas the 'Structure' QVD will list everyone. I've been attempting to map 'null' to provide a Department for any outsourced employees.

Name	Manager	Location	Department	Supplier
[Employee Name 1]	[Manager Name 1]	England	Sales	Company1
[Employee Name 2]	[Manager Name 2]	Scotland	Communications	Company1
[Employee Name 3]	[Manager Name 3]	England	-	Company2
[Employee Name 4]	[Manager Name 4]	Wales	Logistics	Company1
[Employee Name 5]	[Manager Name 5]	Wales	-	Company2
[Employee Name 6]	[Manager Name 6]	Scotland	-	Company2

Luckily, any of the outsourced employees from 'Company2' only need to be placed into the 'Sales' Department.

What I have tried: 

I have tried mapping over those 'Nulls' in various methods. On reflection I'm not entirely sure that is the way forward though - I'm wondering if the issue is because the data doesn't exist, or even a space for it, after loading everything.

I have also tried LEFT JOIN without any differing results.

Any thoughts/help/direction appreciated!

[sunny_talwar]

Where is code where you have mapped Department?

[Luigi_Qlik]

Hi, sorry, didn't think to include it because they were failures. In retrospect I should have so you could see what hasn't worked thus far.

Attempt #1: I've tried a variation of checks for blanks, nulls, hyphens so I'll show these all below as one

DeptNull:
Mapping LOAD * Inline
[
Dept,RenameDept
'','blank'
' ','space'
' ','two spaces'
'-','hyphen'
Null(),'Null'
];

Employees:

LOAD

ApplyMap('DeptNull',Dept),

IF(LEN(TRIM(PurgeChar(Dept,'-0123456789')))>1,TRIM(PurgeChar(Dept,'-0123456789'))) AS Department,

Name,

etc.

Attempt #2:

DeptNull:

MAPPING LOAD
Null(),
'Sales'
AutoGenerate 1; 

MAP Dept USING DeptNull;

[sunny_talwar · Accepted solution]

I think you are trying to add this to the Employee table where the rows don't even exists from what I understand... may be try this instead

tmpStructure:
LOAD Name,
     Location,
     Supplier,
FROM [file](qvd);

Left Join (tmpStructure)
LOAD Name,
     If(Len(Trim(PurgeChar(Dept, '-0123456789'))) > 1, Trim(PurgeChar(Dept, '-0123456789'))) as Department,
     Manager
FROM [file](qvd);

Structure:
NoConcatenate
LOAD Name,
     Location,
     Supplier,
     Manager,
     If(Len(Trim(Department)) > 0, Department, 'Sales') as Department
Resident tmpStructure;

DROP Table tmpStructure;

[asinha1991]

the scenario you have generates empty value and not null, if you want to handle it as null you need to join these tables

and apply transformation on top it for example

joinedTbl:

LOAD

Name,

Location,

Supplier,

FROM [file](qvd);

outer join

Load

IF(LEN(TRIM(PurgeChar(Dept,'-0123456789')))>1,TRIM(PurgeChar(Dept,'-0123456789'))) AS Department,

Name,

Manager

FROM [file](qvd);

FinalTABL:

NoConcatenate;

load all field names, if(isnull(Department),'NA',Department) as Department

Resident joinedTbl;

Drop table joinedTbl;

if you just want this to be done for a chart in frontend and not dataset, you can attempt using aggr( let me know I can share example)

[Luigi_Qlik]

That works beautifully! Thank you very much!