How to count a range of numbers with a prefix number in parenthesis

[Applicable88]

Hi,

I have two tables. One is a Report table with and another table with Shelf ID and Location of it:

Table1			Table2
Report Number		Description of Problem	Shelf ID		Location
3007866		Table broken	(9999)1111		Factory A
3007865		(9999)1111	(9999)2222		Factory C
3007864		Wheel malfunctoin	(9999)1234		Factory A
3007863		(9999)2222	(9999)2221		Factory B
3007862		(9999)1234	(9999)1345		Factory D

The report table is used for every trouble and error records that happens in the company. The field of "Description of Problem" is actually a misuse of that column. But we have no choice, because many other problems are not Shelf related and there could be just a plain string like "Table broken" in the fields of that column. 

So unfortunately my Shelf ID have a prefix like "(9910)" I don't know how to use aggregation to count my shelf related errors. 

Like: 

count({<which setexpression>}report number) 

what to put inside the set expression when the range of shelf ID's is ranging from: (9910)1000-(9910)9999?

I also tried these: count({$<[Description of Problem]={>="(9910)1000"<="(9910)9999)"}>}report number)

Put it with and without inverted comas. Syntax always "wrong"

Thanks in advance.

Best.

[tm_burgers · Accepted solution]

Do you have access to the load script? 

Adding a flag in the load script for any "Description of Problem" that has the substring "(9999)" would be the easiest way to complete this.

Then you would just have to count the flags. 

[Applicable88]

Hi @tm_burgers ,

thanks for your reply.

Will a bar-diagram still knows the difference between (9999)1111 and (9999)1234 for example?

Best regards. 

[tm_burgers · Accepted solution]

Yes, 

You would just set up the Bar Chart with the Dimension - ProblemDescription; and the measure would be a set analysis where the new flag dimension field is "True"; 

count({< FLAG_NAME = {'True'}>} ProblemDescription)

[Saravanan_Desingh]

Are you looking something like this?

tab1:
LOAD *, If(ShelfID#>=99101000 And ShelfID#<=99999999, 'Y', 'N') As Flag;
LOAD *, KeepChar([Description of Problem],'1234567890') As ShelfID#;
LOAD * INLINE [
    Report Number , Description of Problem
    3007866, Table broken
    3007865, (9999)1111
    3007864, Wheel malfunctoin
    3007863, (9999)2222
    3007862, (9999)1234
];

[Saravanan_Desingh · Accepted solution]

Output