topic Load resident table between 2 ranges in QlikView

Load resident table between 2 ranges

marcel_olmo — Wed, 01 Sep 2010 23:47:18 GMT

As you can see in the file I've updated, I have a file with 4 tables :

1-Origen datos (origin data) : Contains all the data information of some kind of scheme account. With idFile, description, type of scheme, and calculation.

2-Cuenta_formula : Contains the Origin data filtered by the type of scheme 'F' (Formula).

3- Cuenta_formula2 : Contains the 'Cuenta_formula' table, but I use the subfield() function to split the separators ( |, +).

4- Cuenta_formula3 : Contains the 'Cuenta_formula2' table, but I added the minimum and the maximum of the range (..). By default if the row has no range, the minimum and maximum value would be the same.

What I want now is to show the whole range of numbers of each row, I mean :

What I have now in the last table, if I select N_Fila = '70' is :

I want to create another table with 'N_Fila' and each number of the range, row by row.

In this case, if you check the original table "origen datos", you'll know that the range between "20..60" is 20,30,40,50,60.

And the final result would be :

N_Fila Result

70 20

70 30

70 40

70 50

70 60

How can I get this result?

I'm completely lost. I tried many combinations about loading two separate residents with left join, noconcatenate, for...loops and I can't get it.

Many thanks by advance.

Cheers!

Load resident table between 2 ranges

Thu, 02 Sep 2010 00:34:37 GMT

If the original table "origen datos" has separate rows for each value - 20,30,40,50,60, within each group (e.g. N_Fila)=70 and your new table has one row for N_Fila=70, then if you do an inner join of the teo tables you will get your desired result.

e.g.

"origen_datos":

n_fila, data

70,20
70,30
70,40
70,50
70,60
80,10
80,20

summary table (cuenta formula2?):
n_fila, other stuff
70, other1
80, other2

if you innner join the two tables on n_fila you will get
n_fila,data,other
70,20,other1
70,30,other1
70,40,other1
70,50,other1
70,60,other1
80,10,other2
80,20,other2

Steve

Load resident table between 2 ranges

marcel_olmo — Thu, 02 Sep 2010 16:44:48 GMT

Thank you for your answer Steve. But this is not what I want, it's kind of complicated.

My last parsing resident table is "Cuenta_Formula3" and in there I have a number and the range of numbers that belongs to him.

The list of all the numbers is in the "Origen Datos" Table, so I have to do some kind of merge between "OrigenDatos" and "Cuenta_Formula3" to get the range of numbers that I want.

I mean, if you select N_Fila = '70' in the attached file, you'll see in the table "Cuenta_Formula3" :

N_Fila Range
70 20..60

And what I need is to get the numbers between 20 and 60, and this information is inside the "OrigenDatos" table, and the final result would be :

N_Fila Result

70 20

70 30

70 40

70 50

70 60

Thanks for trying to help me Steve.

Anybody knows what can I do in this kind of situations? I'm completely stuck.

Thanks in advance.

Cheers!

Load resident table between 2 ranges

marcel_olmo — Thu, 02 Sep 2010 19:25:05 GMT

I know that the solution is close, I tried left joins, right joins, inner joins, concatenates, non concatenates.

Also tried for loops triying to merge the two tables with mapping tables. But for me there is no way.

Anybody could help me with this stuff?

Thanks.

Regards.

Load resident table between 2 ranges

johnw — Thu, 02 Sep 2010 22:29:08 GMT

Not sure if I understood the question, but tacking this onto the end of your load script appears to produce the result you asked for:

Results:
LOAD num(fieldvalue('N_Fila',iterno())) as Result
AUTOGENERATE 1
WHILE len(fieldvalue('N_Fila',iterno()))
;
LEFT JOIN (Cuentas_Formula3)
INTERVALMATCH (Result)
LOAD min, max
RESIDENT Cuentas_Formula3
;
DROP TABLE Results;

Load resident table between 2 ranges

marcel_olmo — Fri, 03 Sep 2010 08:51:59 GMT

Thank you very much John!! I appreciate a lot your help. I'm pretty new here and this kind of stuff is hard work for me. I got tired struggling my head.

I only need to add this last one resident to get the desired result :

Cuentas_Formula4 :

load distinct

N_Fila, Result

resident Cuentas_Formula3;

drop table Cuentas_Formula3;

Thank you. See you around!!!

Re: Load resident table between 2 ranges

Wed, 11 Jul 2012 10:45:26 GMT

why

LOAD num(fieldvalue('N_Fila',iterno())) as Result

AUTOGENERATE 1

WHILE len(fieldvalue('N_Fila',iterno()))

and not

LOAD num(fieldvalue('N_Fila',iterno())) as Result

AUTOGENERATE 1

WHILE fieldvalue('N_Fila',iterno())