Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
LINKEDIN LIVE: Democratizing data to enhance customer-centricity. JULY 29TH REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Sai_Mohan
Contributor III
Contributor III

Removing single value in a column

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.

Labels (1)
2 Solutions

Accepted Solutions
nsm1234567
Creator II
Creator II

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

View solution in original post

Ksrinivasan
Specialist
Specialist

hi,

dummy data:

Ksrinivasan_0-1610960738533.png

 

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:

Ksrinivasan_2-1610960915337.png

 


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_1-1610960858956.png

ksrinivasan

View solution in original post

7 Replies
Ksrinivasan
Specialist
Specialist

hi,

you can filter null rows from script itself,

use below script after from file path:

where not match (approver ,'');

 

ksrinivasan

Sai_Mohan
Contributor III
Contributor III
Author

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
Creator II
Creator II

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
Creator II
Creator II

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

View solution in original post

Ksrinivasan
Specialist
Specialist

hi,

dummy data:

Ksrinivasan_0-1610960738533.png

 

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:

Ksrinivasan_2-1610960915337.png

 


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_1-1610960858956.png

ksrinivasan

View solution in original post

Sai_Mohan
Contributor III
Contributor III
Author

Thanks for the help @Ksrinivasan !

Sai_Mohan
Contributor III
Contributor III
Author

Thanks for the help @nsm1234567 !.

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