Skip to main content

New to Qlik Sense

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

Announcements
QlikWorld 2023, a live, in-person thrill ride. April 17 - 20, 2023, in Las Vegas! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
felicity13
Contributor
Contributor

Filtering of a specific text and count it

Hi I am new to Qlik sense. 

Currently want to try out some data. 

I have problem to filter a specific text such as "add" and then count the total number of "add" and show in a table. 

My data will be in a column and show, S_add, Add_test, A_test_add, S_test and etc.

Can anyone can me with this?

Thanks in advance.

10 Replies
xufei123
Partner - Specialist
Partner - Specialist

=sum(if(index(ColumnName,'add')>0,1,0))

felicity13
Contributor
Contributor
Author

Hi,

Tried not working.

Below is roughly how the table look like.

Item                   Date                        Files

EQ1                 2019/08/14             A_add

EQ2                2019/08/15             A_add_test

EQ3                 2019/08/16             A_test

EQ3                 2019/09/15             A_S_Add

EQ1                 2019/09/14             A_Test_Add

EQ1                 2019/09/15            A_test

EQ3                 2019/07/14            A_test

EQ2                 2019/07/15            A_Test_Add

EQ1                 2019/07/15             A_Test_Add

EQ3                 2019/08/11             A_Test_Add

EQ2                 2019/08/11             A_add

EQ2                 2019/08/16             A_add

EQ3                 2019/09/11             A_add

EQ1                 2019/09/11             A_add

EQ3                 2019/07/10             A_test

EQ2                 2019/08/10             A_test

I have difference items, date and file.

I would like to filter the items in one group with date and total number of "add" 

felicity13
Contributor
Contributor
Author

Hi,

 

Not working..

felicity13
Contributor
Contributor
Author

Hi,

 

Maybe to have clearer picture of what i want.

Items           Date                        Files

EQ1             2019/01/01           S_add

EQ1             2019/01/02           Add_Test

EQ3             2019/01/02           S_Test_add

EQ2             2019/02/02           Add_Test

EQ1             2019/02/01           S_Test

EQ3             2019/03/02           A_Test

EQ2             2019/03/02           S_add

EQ2             2019/02/02           Add_Test

EQ3             2019/01/01           S_add_test

EQ1             2019/02/05           Add_Test

EQ1             2019/01/10           S_add_test

EQ3             2019/03/22           A_Add_Test

EQ3             2019/03/01           S_Test

EQ2             2019/01/22           A_Test

I need to group the data in month with "add" in diff Items group.

May I know if there is any problem?

I need the script. 

Thank you very much for the help. 

Yoshidaqlik
Creator II
Creator II

Hi

can try
if(wildmatch(Files, '*add*')>0,'new_group',Files)
to a different grouping

or for a measure can be
count (if(wildmatch(Files, '*add*')>0,Files))

regards
Rafael Yoshida
YoshidaQlik https://www.youtube.com/channel/UC1I9P8MqCZEhB6Nw3FdSqng
ChennaiahNallani
Creator III
Creator III

Try like below

 

T:
Load Items,Date(Date#(Date,'YYYY/MM/DD'),'YYYY/MM/DD') as Date,Files,if(wildmatch(Files, '*add*')>0,1,0) as Files_Count;
Load * Inline [
Items,Date,Files
EQ1,2019/01/01,S_add
EQ1,2019/01/02,Add_Test
EQ3,2019/01/02,S_Test_add
EQ2,2019/02/02,Add_Test
EQ1,2019/02/01,S_Test
EQ3,2019/03/02,A_Test
EQ2,2019/03/02,S_add
EQ2,2019/02/02,Add_Test
EQ3,2019/01/01,S_add_test
EQ1,2019/02/05,Add_Test
EQ1,2019/01/10,S_add_test
EQ3,2019/03/22,A_Add_Test
EQ3,2019/03/01,S_Test
EQ2,2019/01/22,A_Test
];

T:
Load
Month(Date) as Month,
sum(Files_Count) as Total_Conut
Resident T
Group by Month(Date);

 

 

felicity13
Contributor
Contributor
Author

Hi,

Below table is an example. I have full long list of data.

 

Thanks for the help.

felicity13
Contributor
Contributor
Author

Hi,

Tried. Not working.

My data can go up to thousands. 

if(wildmatch(Files, '*add*')>0,Files) -----> this work

count (if(wildmatch(Files, '*add*')>0,Files)) -----------> this cannot work. Tried it.

Yoshidaqlik
Creator II
Creator II

for counting maybe ?

Sum (if(wildmatch(Files, '*add*')>0,1)) 

YoshidaQlik https://www.youtube.com/channel/UC1I9P8MqCZEhB6Nw3FdSqng