Removing single value in a column

[Sai_Mohan]

Hi All,

I have column named Employees and approvers. For each employees, there will be three approvers.

So each employee will have three rows. 

For example:

 Bob is employee and he has three approvers(Chris, John, Philip). These values will be in three different rows.

My question is few employee has only two or one approver, so for remaining values i have given Null. Can we remove only null values from that  approver column.

[Ksrinivasan]

hi,

you can filter null rows from script itself,

use below script after from file path:

where not match (approver ,'');

ksrinivasan

[Sai_Mohan]

Thanks for the help @Ksrinivasan !

I have used crosstable for this approver column to values in three rows. below is my script.

Load Script:

ApproverName:
CrossTable(ApproverLevel,ApprovedBy,1)
load Distinct
emp,
APPROVED_ab ,
APPROVED_cd ,
APPROVED_OTHER
Resident Tab

WHERE NOT MATCH(ApprovedBy, 'Unknown');            // Iam getting error for using this.

Please help me with this.

[nsm1234567]

Hey there,

I've attached some scripting to do this.  Simplest  would just be with a where, in this case taking advantage of the fact that the len of a null value in qlik is zero.  I would not test using a match and '' as suggested in the other post as blank and null are not equivalent.  I also added an example in the attached using a right-keep.  I find that removing data this way is more efficient when you're scaling to very large datasets (but if that's not your use cases, the simple where is fine).

//Load in dummy data and treat empty columns as null
APPROVERS:
LOAD Employee,
     if(len(Approvers)=0,Null(),Approvers) as Approvers;
LOAD * INLINE [
    Employee, Approvers
    Chris, Approver1
    Chris, Approver2
    Chris, Approver3
    John, Approver1
    John, Approver2
    John
    Philip, Approver1
    Philip
    Philip
];


//Option1
REMOVE_APPROVERS:
NoConcatenate
LOAD *
Resident APPROVERS
where len(Approvers) > 0;

drop table APPROVERS;

exit SCRIPT;

[nsm1234567 · Accepted solution]

The ApprovedBy column doesn't exist until the CrossTable has run.  So you'd need to do something like this:

ApproverName:
CrossTable(ApproverLevel,ApprovedBy,1)
load Distinct
emp,
APPROVED_ab ,
APPROVED_cd ,
APPROVED_OTHER
Resident Tab;

TEMP:
LOAD *
Resident ApproverName
WHERE NOT MATCH(ApprovedBy, 'Unknown');
drop table ApproverName;
rename table TEMP to ApproverName;

For future reference, it would help to give an example of your data in these posts to help us better understand what you're trying to do

[Ksrinivasan · Accepted solution]

hi,

dummy data:

ApproverName:
CrossTable(ApproverLevel,ApprovedBy)
load Distinct
emp,
APPROVED_ab,
APPROVED_cd,
APPROVED_OTHER
FROM [lib:///TEST/SSSS1.xlsx]
(ooxml, embedded labels, table is [crosstable null]);
Store ApproverName into [lib://Cross.csv](txt);
drop table ApproverName;

Temp Result:

hi:
LOAD
emp,
ApproverLevel,
ApprovedBy
FROM [lib://TEST/Cross.csv]
(txt, utf8, embedded labels, delimiter is ',', msq)
where not match (ApprovedBy ,'Unknown');
Final result: with Where Class using

ksrinivasan

[Sai_Mohan]

Thanks for the help @Ksrinivasan !

[Sai_Mohan]

Thanks for the help @nsm1234567 !.

I have included  "no concatenate" for the Temp Table, else am getting error like 'ApprovedBy' field not found'