Fixed the length and structure

[sunil-kumar5]

Hi All,

I have a table where I just want that my table should only contain the ID column value in the proper structure which include first five with alphabet next three should be numeric and end with an alphabet so total length is 9, so as per below table I should only fetch the data for 4 rows (Name1,4,7 and Name10) only in a table. Please advise how to achieve this.

Name	ID
NAME1	ASDFL302T
NAME2	ERRDSKFDFFDFG
NAME3	ADSF
NAME4	LKJGH786F
NAME5	456SDFFJG
NAME6	T567LKHDD
NAME7	ASERF987G
NAME8	855898RREWRER
NAME9	WQEREWT3
NAME10	BJHGC456P

Thanks

Sunil Kumar

[squreshi · Accepted solution]

 Hi Sunil,

You can filter the data at the script level for a fixed length of characters to 9 using Len(ID)=9 in the where clause.

And extract the numbers from the ID column using the Mid function.

And then use the ISNUM function in the IF condition of the table to get the required result.

Please mark the solution as correct if you find it helpful and correct.

Thanks,

Squreshi

[Taoufiq_Zarra · Accepted solution]

@sunil-kumar5  if I undersood correctly

you can use Flag for this or use this function in a filtre

if(len(purgechar(left(ID,5),'0123456789'))=5 and len(KeepChar(mid(ID,6,3),'0123456789'))=3 and len(purgechar(right(ID,1),'0123456789'))=1,1,0),0)

like this in Script:

LOAD *,if(len(ID)=9,if(len(purgechar(left(ID,5),'0123456789'))=5 and len(KeepChar(mid(ID,6,3),'0123456789'))=3 and len(purgechar(right(ID,1),'0123456789'))=1,1,0),0) as Flag INLINE [
    Name, ID
    NAME1, ASDFL302T
    NAME2, ERRDSKFDFFDFG
    NAME3, ADSF
    NAME4, LKJGH786F
    NAME5, 456SDFFJG
    NAME6, T567LKHDD
    NAME7, ASERF987G
    NAME8, 855898RREWRER
    NAME9, WQEREWT3
    NAME10, BJHGC456P
];

output:

the you can use this Flag in Set analysis like sum({<Flag={1}>} Field)

or in load Script where Flag=1

...

[Vegar · Accepted solution]

You can also use @Taoufiq_Zarra's logic for filtering while loading your data, like this.

LOAD *
INLINE [
    Name, ID
    NAME1, ASDFL302T
    NAME2, ERRDSKFDFFDFG
    NAME3, ADSF
    NAME4, LKJGH786F
    NAME5, 456SDFFJG
    NAME6, T567LKHDD
    NAME7, ASERF987G
    NAME8, 855898RREWRER
    NAME9, WQEREWT3
    NAME10, BJHGC456P
]
WHERE
    len(ID)=9 
    AND len(purgechar(left(ID,5),'0123456789'))=5 
    AND len(KeepChar(mid(ID,6,3),'0123456789'))=3 
    AND len(purgechar(right(ID,1),'0123456789'))=1 
;

[sunil-kumar5]

Thank you so much, you all have given the great response and I am able achieve this.

[Saravanan_Desingh · Accepted solution]

One more solution, using pattern match. A- Alpha; N - Num

tab1:
LOAD *, If(Index('ABCDEFGHIJKLMNOPQRSTUVWXYZ',Mid(ID,IterNo(),1)),'A',
	If(Index('1234567890',Mid(ID,IterNo(),1)),'N',Mid(ID,IterNo(),1))) As P1,
	IterNo() As S1
While IterNo()<=Len(ID);
LOAD * INLINE [
    Name, ID
    NAME1, ASDFL302T
    NAME2, ERRDSKFDFFDFG
    NAME3, ADSF
    NAME4, LKJGH786F
    NAME5, 456SDFFJG
    NAME6, T567LKHDD
    NAME7, ASERF987G
    NAME8, 855898RREWRER
    NAME9, WQEREWT3
    NAME10, BJHGC456P
];

Left Join(tab1)
LOAD Name, ID, If(Concat(P1,'',S1)='AAAAANNNA','Y','N') As Flag
Resident tab1
Group By Name, ID;