topic Re: How to use * in load script in QlikView

How to use * in load script

Thu, 30 Oct 2014 13:38:34 GMT

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!!!!

Re: How to use * in load script

JonnyPoole — Thu, 30 Oct 2014 13:42:35 GMT

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.

Re: How to use * in load script

Thu, 30 Oct 2014 13:43:08 GMT

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,

Re: How to use * in load script

its_anandrjs — Thu, 30 Oct 2014 13:47:03 GMT

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

Regards

Anand

Re: How to use * in load script

anbu1984 — Thu, 30 Oct 2014 13:49:30 GMT

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;