topic Auto ranges in QlikView

Auto ranges

Anonymous — Mon, 21 Apr 2014 08:05:15 GMT

Hi Team,

Can any one help me in acheiving the below task.

Below is the screenshot of my raw data

SEQ_Nr	From	To	Match
1	230	235	10100
1	236	239	10101
2	230	235	10100
2	236	239	10101@

What I want to achive here is I want to map the ranges with one of my other tables.

Say for 2301 I need the answer 10100 and 2311 10100 and for 2361 I need 10101,2302-10100 etc...

The script has to automatically creates the mapping/range table based on three different combinations for the ranges,from that mapping I can do the matc with my other table.

It will be great if any one of the experts helps me with this.

Regards

Jeba

Re: Auto ranges

anbu1984 — Mon, 21 Apr 2014 08:21:14 GMT

In Sql,

Select t2.col, t1.match

from Table1 t1, Table2 t2

where t2.col between t1.From and t1.To;

Re: Auto ranges

Anonymous — Mon, 21 Apr 2014 08:29:51 GMT

Hi Anbu,

Thanks for the immediate reply.

I have one more clarification in this logic.

My t2 has only the details like 2301,2302 etc.So in my t1 I need to consider my SEQ_Nr also to match.

It will be great if you could help me in creating the resident table of my t1 which will consider all three fields(Seq_nr,From,To).

Jeba

Re: Auto ranges

anbu1984 — Mon, 21 Apr 2014 08:39:11 GMT

Does table2 T2 has SEQ_Nr?

Re: Auto ranges

Anonymous — Mon, 21 Apr 2014 08:40:49 GMT

Re: Auto ranges

anbu1984 — Mon, 21 Apr 2014 08:43:38 GMT

What is the rule to select SEQ_Nr?

Re: Auto ranges

Mon, 21 Apr 2014 09:01:16 GMT

please try this

TestData7:

load *

inline [

SEQ_Nr,From,To,Match

1,230,235,10100

1,236,239,10101

2,230,235,10100

2,236,239,10101

];

Let vNumberOfRows = NoOfRows('TestData7');

For vI = 0 to $(vNumberOfRows) - 1

Let vFrom = Peek('From',vI,'TestData7');

Let vTo = Peek('To',vI,'TestData7');

Let vMatch = Peek('Match',vI,'TestData7');

Let vSEQ_Nr = Peek('SEQ_Nr',vI,'TestData7');

T1:

LOAD

($(vFrom)+RecNo()-1)*10+$(vSEQ_Nr) AS From,

$(vMatch) AS Match

AutoGenerate($(vTo)-$(vFrom)+1)

;

DROP TABLE TestData7;

Re: Auto ranges

Anonymous — Mon, 21 Apr 2014 10:06:50 GMT

Thanks a lot for the quick solution.

I will try this one and message me if any issues.

Jeba