Set Analisys and Function Mid problem

[#1]

We have the field [JRQUIA_Sales] with this data:

2013/12/0051005230/0003100043E0/1100

2013/12/0051005230/0006000060D6/1100

2013/12/0051005230/000100000101/1100

2013/12/0051005237/000100000101/1100

2013/12/0051005305/000100000101/1100

2013/12/0051005305/000100000102/1100

2013/12/0051005348/0003100043E0/1100

2013/12/0051005348/000100000101/1100

2013/12/0051005348/000100000102/1100

2013/12/0051005353/000100000101/1100

2013/12/0051005353/000100000102/1100

2013/12/0051005360/000100000101/1100

2013/12/0051005366/000100000101/1100

With a set analisys we need to extract from [JRQUIA_Sales] only the registers if

Mid([JRQUIA_Sales],20,10) = {'0001000001','0001000002','0001000004','0002000020','0002000021','0002000022','0003000040','0003000041','0003100043','0005000050','0006000060'}

But this code don't works:

Sum ({$<Mid([JRQUIA_Sales],20,10)={'0001000001','0001000002','0001000004','0002000020','0006000060'}>}

Sales)

Thanks.

[swuehl]

You can't use a function on the left side of a field modifier, only field names.

So it would be easiest to create an additional field using the mid() function in your script, then apply the field modifier value list to that field.

Or maybe use a search string:

Sum ({$< [JRQUIA_Sales] = {"*0001000001*","*0001000002*","*0001000004*","*0002000020*","*0006000060*"}>}

Sales)

[Josh_Good]

I would recommend creating a new field when you load your data that has just the data you need to filter on in the set analysis.  This will make the set analysis trivial and improve performance.

Sum ({$< [JRQUIA_Sales_NEW_FEILD] = {'0001000001','0001000002','0001000004','0002000020','0006000060'}>}

Sales)

You could simplify it even more by creating a mapping table in your load script and then using that to create a flag for the records to include.  That would be make your set analysis dead simple.

Sum ({$< [IncludeRecordFlag] = {1}>} Sales)

[jagan]

HI,

Create a new flag in script and use that flag in set analysis, it is a straight forward approach.  Try like this

Data:

LOAD

*,

If(Match(Text(Mid([JRQUIA_Sales],20,10)), '0001000001','0001000002','0001000004','0002000020','0006000060'), 1, 0) AS Flag

FROM DataSource;

Now in Set analysis you just use

Sum ({$<Flag = {1}>} Sales)

Hope this helps you.

Regards,

Jagan.