Challenging problem, merging two resident tables.

marcel_olmo — Wed, 01 Sep 2010 17:54:34 GMT

Hello guys, I really got stuck with this stuff, and I want to know if it's possible to merge two resident tables to get the following result :

I have an inline data which shows lines of scheme accounts.

I need to separate the accounts of type 'F'. --> I've done it

And after that I need to split the separators '/' and '+'. --> I've done it.

I got stuck with this table (which shows ranks of numbers) :

I need to show the numbers from the minimum rank number to the last rank number.

I need to pick the "N_Fila" values from the "OriginTable" because in there I have all the possible values, and compare it with the "min" value and the "max" value of my table.

For example : I now that inside the rank "20..60" there is 20,30,40,50,60 because these numbers are in the original list.

I was thinking about some king of crossing two resident tables, but I'm completely lost.

My desired result would be :

N_Fila Sumatorio

70 20

70 30

70 40

70 50

70 60

80 10

80 70

Now I give you my code, to make it easier :

OrigenDades :

load *
inline [

N_Fila ,Descripcion,Tipo_Sumatorio,Sumatorio

10,FACTURACION BRUTA,Cuentas mayor,7000011..7010003|7050001..7050003|7050005..7056004|7080001..7080003|6030001..6030004|7030001..7030004|7320001
20,Comisiones Externas,Cuentas mayor,6230000..6230010
30,Montajes ,Cuentas mayor,6070002..6070005
40,Transportes Asignables,Cuentas mayor,6240001..6240002|6240004..6240011|6240020..6240021|7056005|6240015
50,Transportes Muestras,Cuentas mayor,6240013|6240023|6240018|6240019
60,Transportes NO Asignables,Cuentas mayor,6240000|6240500|6240014|6240016|6240017
70,TOTAL GASTOS VARIABLES,F,20..60
80,FACTURACION NETA,F,10|70
90,TOTAL CONSUMO MATERIA PRIMA,Cuentas mayor,6000001..6010004|6110001..6110001|7020001..7020004|7120000..7120001|6091001
100,MARGEN EXPLOTACIÓN,F,80+90
110,Sueldos y Salarios,Cuentas mayor,6400001..6400007|7550001..7550001|6230211|6290701
120,Incentivos Salariales,Cuentas mayor,6400008..6400100
130,Retribuciones Especie,Cuentas mayor ,6210009|6210012|6210013|6210015|6210017|6210019|6210021|6210023|6210024|6210025|6210026
140,Indemnizaciones Cuentas,Cuentas Auxiliar,6410001

];

Cuentas_Formula :

load

N_Fila as N_Fila,
Sumatorio as Sumatorio_Formula
resident OrigenDades where "Tipo_Sumatorio" like 'F';

Cuentas_Formula2 :

load

N_Fila,

if ( subfield(Sumatorio_Formula,'+') ,
subfield(Sumatorio_Formula,'+') ,

if( subfield(Sumatorio_Formula,'|') ,

subfield(Sumatorio_Formula,'|'),

Sumatorio_Formula )

) as Sumatorio_Formula2

resident Cuentas_Formula where "N_Fila"<>'540';

Cuentas_Formula3 :

load

N_Fila,

Sumatorio_Formula2 as Sumatorio_Formula3 ,

if( left(Sumatorio_Formula2,index(Sumatorio_Formula2,'..')-1), left(Sumatorio_Formula2,index(Sumatorio_Formula2,'..')-1) ,Sumatorio_Formula2 ) as min,

if(right(Sumatorio_Formula2,index(Sumatorio_Formula2,'..')-1), right(Sumatorio_Formula2,index(Sumatorio_Formula2,'..')-1),Sumatorio_Formula2 ) as max

resident Cuentas_Formula2;

Temp_Counter_Formula :

mapping load

N_Fila,

count(N_Fila)as Fila_Counter

resident Cuentas_Formula3 group by N_Fila;

set nMaxFila = fieldValueCount('N_Fila');

//for i=0 to $(nMaxFila)
//
//set nMaxFila2 = applymap('Temp_Counter_Formula',peek($(i))) ;
//
// for j=0 to $(nMaxFila2)
//
//// Temp_dades :
////
//// load
////
//// N_Fila,
////
//// // CONCAT(N_Fila, ',',Orden_N_Fila)
////
//// if ( N_Fila <= min,'a','b' ) AS IdOrigenDades
////
////
//// resident OrigenDades ;
//
// next
//
//next

Any help would be appreciated.

Many thanks in advance.

topic Challenging problem, merging two resident tables. in QlikView

Challenging problem, merging two resident tables.