How to use * in load script

[#1]

Hi All,

I want to show the data where Ids should start with first digit if roleid is 1.

eg.

RoleId, Ids

1, 1000

1, 1001

2, 1201

2, 1202

2, 1203

3, 1230

3, 1231

If RoleId =1 then Result should be

Ids

1000

1001

1201

1202

1203

1230

1231

means first digit should match.

If RoleId =2 then Result should be

Ids

1201

1202

1203

1230

1231

means first two digits should match.

I want to use the * in the script.

  If(RoleID=1,Left(Ids,1)&'*') AS Ids_1,

can anyone tell me the correct way to do this.

Thanks!!!!

[JonnyPoole]

the Mid() function will help extract a single character from a dynamic placement

->  Mid( Ids, RoleId,1)

This will start at character position 'RoleId'  (1,2,3 ..changes for every row) and take 1 character out of the Ids value for that row.

I think its what you may want . but please clarify.

[#3]

you can use wildmatch function, to use * as equivalent to Like in SQL.

But for your requirement.

if it will have only 4 digits then you can use

If(RoleID=1,Left(Ids,1)&right(Ids.3)) AS Ids_1,

[its_anandrjs]

Please explain about the input table and final output table because it is not understood with any example.

Regards

Anand

[anbu1984]

Temp:

Load *,1 As Key Inline [

RoleId,Ids

1,1000

1,1001

2,1201

2,1202

2,1203

3,1230

3,1231 ];

Join

Load Distinct 1 As Key,Left(Ids,RoleId) As Mtch  Resident Temp Where RoleId=2 ;

NoConcatenate

Final:

Load * Resident Temp Where Ids Like Mtch & '*';

Drop Table Temp;