topic Re: Conversion of function IntervalMatch () in QlikView script in QlikView

Conversion of function IntervalMatch () in QlikView script

Fri, 06 May 2011 11:45:12 GMT

Hi community.

Let two tables are , and suppose that the function IntervalMatch () does not exist.

The problem is: we want to link the two tables as IntervalMatch() function proceed.

Could someone offer me a QlikView script that makes it ?

I attached a file that QlikView presents the two tables.

Thanks in advance for ur help.

Best regards.

Re: Conversion of function IntervalMatch () in QlikView script

rwunderlich — Fri, 06 May 2011 14:37:47 GMT

Please explain your rules for matching and show the desired result. Does JCM match CA-RC, MTA-XBT or both?

Conversion of function IntervalMatch () in QlikView script

Fri, 06 May 2011 15:00:57 GMT

The results I expect are:

Heure must be between Debut and Fin

CB must be between CA and RC

MA must be between CA and RC

RD should be linked to anything

CC must be between CA and RC

JCM must be between JCP and JST

...

and so on

Thanks

Conversion of function IntervalMatch () in QlikView script

Oleg_Troyansky — Fri, 06 May 2011 15:28:10 GMT

As a work around, you could create a distinct list of all alphanumeric codes, sorted alphabetically, then assign numbers to all codes, and then join using IntervalMatch with the numeric representations of the alphanumeric codes.

Conversion of function IntervalMatch () in QlikView script

Fri, 06 May 2011 15:49:14 GMT

Please Oleg, with an example, i will hear very well.

there is an attached file at the post.

Thanks

Conversion of function IntervalMatch () in QlikView script

Oleg_Troyansky — Fri, 06 May 2011 15:57:10 GMT

Unfortunately, I'm a bit busy, so you'll need to write your own code this time, Harry Potter. I gave you an idea, and you can run with it. Sorry!

Re: Conversion of function IntervalMatch () in QlikView script

johnw — Fri, 06 May 2011 22:49:45 GMT

If I've understood both your question and what Oleg was suggesting, see attached. Script below. The fieldvalue() approach is overkill for small tables like this where a load distinct would work just fine. However, it's faster if your tables are large and have a lot of repeated text values, so I'm just in the habit of extracting distinct values this way.

Text:
LOAD text(fieldvalue('Debut',iterno())) as Text
AUTOGENERATE 1
WHILE len(fieldvalue('Debut',iterno()))
;
OUTER JOIN (Text)
LOAD text(fieldvalue('Fin',iterno())) as Text
AUTOGENERATE 1
WHILE len(fieldvalue('Fin',iterno()))
;
OUTER JOIN (Text)
LOAD text(fieldvalue('Heure',iterno())) as Text
AUTOGENERATE 1
WHILE len(fieldvalue('Heure',iterno()))
;
LEFT JOIN (Text)
LOAD
Text
,recno() as Sequence
RESIDENT Text
ORDER BY Text
;
LEFT JOIN (JournalCommandes)
LOAD
Text as Debut
,Sequence as DebutSeq
RESIDENT Text
;
LEFT JOIN (JournalCommandes)
LOAD
Text as Fin
,Sequence as FinSeq
RESIDENT Text
;
LEFT JOIN (JournalEvenements)
LOAD
Text as Heure
,Sequence as HeureSeq
RESIDENT Text
;
//DROP TABLE Text; // You'd drop the table in a real application.
// I'm leaving it here so you can see the table.

ZoneTable:
IntervalMatch (HeureSeq) LOAD DebutSeq, FinSeq RESIDENT JournalCommandes;

Re: Conversion of function IntervalMatch () in QlikView script

Sat, 07 May 2011 11:39:57 GMT

Thanks sir John.

But I can not adapt it to the situation in the attached file.

Watch DEBCPTE and FINCPTE fields in the table FORMULE.

Everything should be over 8 characters and not 6 as I did in the script.

Thank you.

Re: Conversion of function IntervalMatch () in QlikView script

Sun, 08 May 2011 11:38:34 GMT

Not able to open your latest zip file (qlik_state_new.qvw.zip),

but based on the first post managed to get the interval match except for JCM which cannot come under bucket (JCP - JST).

JCM gets a lower numeric value than JCP!

Assumptions:

1. Text values only from A to Z (only capitals) in fields Debut, Fin and Heure

2. Text values will have max 8 strings - max can be changed

3. Field "Debut" is the Start Value and "Fin" is the End Value

Method used:

1. Converted each occurence of text string to a 2 digit numeric number from 01 to 26

2. Interval match based on the concatenated numeric values

Hope this works for you!