topic Re: Set Analisys and Function Mid problem in QlikView

Set Analisys and Function Mid problem

Thu, 19 Dec 2013 16:12:45 GMT

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.

Re: Set Analisys and Function Mid problem

swuehl — Thu, 19 Dec 2013 16:19:37 GMT

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)

Re: Set Analisys and Function Mid problem

Josh_Good — Thu, 19 Dec 2013 23:47:35 GMT

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)

Re: Set Analisys and Function Mid problem

jagan — Fri, 20 Dec 2013 02:17:41 GMT

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.