Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

Return only number with a specific format

Hello,

I have a error table with a column , called "descriptions" where you can write inside practically everything:

Date ReportDescriptionValid fromValid until
09.06.202011111111(9999)555501.01.202031.12.2999
09.06.202011111111Table08.06.202031.12.2999
09.06.202011111111stock01.01.202031.12.2999
09.06.202011111111(9999)123408.06.202031.12.2999

 

I only want these values returned which looks like (9999)####.

 

How to write that expression?

Thanks in advance. 

Best. 

3 Solutions

Accepted Solutions
Highlighted
Specialist III
Specialist III

Hi, try with :

=if(wildmatch(Description,'*(9999)*'), Description)

View solution in original post

Highlighted
Specialist III
Specialist III

I don't think there's a single-line way to do it as you would with a regular expression, but pairing two function sets should do it:

WildMatch(Description,'(9999)????') AND IsNum(Right(Description,4))

 

View solution in original post

Highlighted
Partner
Partner

Hi,

Good Day😊

As per your requirement you need an field with the values like format (9999)#### and exclude the string values.

So you can use the substringcount either in script or chart.

In script create the additional field and use it in the front end

i have included the example code,

Table1:
Load * Inline [
KPI,Target,Budget,Quarter,Description
Sales KPI,80,75,Q1,(9999)5555
Sales KPI,80,78,Q2,Table
Sales KPI,72,85,Q3,Stock
Sales KPI,78,80,Q4,(9999)1234
];

Table2:

load
Description,
if(SubStringCount(Description,'(9999)'),Description) as Description1
Resident Table1;

the field with string values could be neglected and number with the correct format only printed.

snip1.PNG

after checking the suppress when value is null for description dimension it gives the below output,

snip2.PNG

Meanwhile, it is performed in the front end by creating the calculated dimension 

=if(Substringcount(Discription,'(9999)'),Discription,'') 

Hope it is helpful

Regards,

DurgadeviKumar

View solution in original post

6 Replies
Highlighted
Specialist III
Specialist III

Hi, try with :

=if(wildmatch(Description,'*(9999)*'), Description)

View solution in original post

Highlighted
Specialist III
Specialist III

I don't think there's a single-line way to do it as you would with a regular expression, but pairing two function sets should do it:

WildMatch(Description,'(9999)????') AND IsNum(Right(Description,4))

 

View solution in original post

Highlighted
Creator II
Creator II

Can you write me a set analysis kind of function?

Highlighted
Creator II
Creator II

also as set analysis expressed possible?

Highlighted
Partner
Partner

Hi,

Good Day😊

As per your requirement you need an field with the values like format (9999)#### and exclude the string values.

So you can use the substringcount either in script or chart.

In script create the additional field and use it in the front end

i have included the example code,

Table1:
Load * Inline [
KPI,Target,Budget,Quarter,Description
Sales KPI,80,75,Q1,(9999)5555
Sales KPI,80,78,Q2,Table
Sales KPI,72,85,Q3,Stock
Sales KPI,78,80,Q4,(9999)1234
];

Table2:

load
Description,
if(SubStringCount(Description,'(9999)'),Description) as Description1
Resident Table1;

the field with string values could be neglected and number with the correct format only printed.

snip1.PNG

after checking the suppress when value is null for description dimension it gives the below output,

snip2.PNG

Meanwhile, it is performed in the front end by creating the calculated dimension 

=if(Substringcount(Discription,'(9999)'),Discription,'') 

Hope it is helpful

Regards,

DurgadeviKumar

View solution in original post

Highlighted
Creator II
Creator II

Hello @Durgadevikumar ,

thank you very much!