topic Re: Filtering of a specific text and count it in Visualization and Usability

Filtering of a specific text and count it

felicity13 — Wed, 21 Aug 2019 02:26:25 GMT

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.

Re: Filtering of a specific text and count it

NZFei — Wed, 21 Aug 2019 03:11:23 GMT

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

Re: Filtering of a specific text and count it

felicity13 — Wed, 21 Aug 2019 03:28:17 GMT

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"

Re: Filtering of a specific text and count it

felicity13 — Wed, 21 Aug 2019 05:57:11 GMT

Hi,

Not working..

Re: Filtering of a specific text and count it

felicity13 — Wed, 21 Aug 2019 06:03:14 GMT

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.

Re: Filtering of a specific text and count it

Yoshidaqlik — Wed, 21 Aug 2019 10:38:50 GMT

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

Re: Filtering of a specific text and count it

ChennaiahNallani — Wed, 21 Aug 2019 11:18:50 GMT

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);

Re: Filtering of a specific text and count it

felicity13 — Thu, 22 Aug 2019 01:43:52 GMT

Hi,

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

Thanks for the help.

Re: Filtering of a specific text and count it

felicity13 — Thu, 22 Aug 2019 01:46:00 GMT

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.

Re: Filtering of a specific text and count it

Yoshidaqlik — Thu, 22 Aug 2019 10:15:50 GMT

for counting maybe ?

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

Re: Filtering of a specific text and count it

felicity13 — Fri, 23 Aug 2019 02:02:22 GMT

Hi,

Tried. Not working still.

Thank you very much for the help.

Is there other script?