Validate 3 fields to find a value

[#1]

Hi everyone,

Sorry, but my english isn't the best, hope that you can understand and help me...

I have a problem that i can't work out... i need some way to validate 3 fields to find a value, as follows. The following is a table in Excel, which listed three conditions for which the fee depends on three informations: 1. TRG_RANGE - 2. OWNER - 3. PORT and once the three are validated, determine whether the rate is 800 or 1000, depending on  RAM Owner, only if the port is Antofagasta and the TRG Range is between 0 and 800.

TRG_Range	Owner	Port	Tarifa
0 -> 8000	STACK	ANTOFAGASTA	800,00
0 -> 8000	TICKELS	ANTOFAGASTA	1.000,00

Moreover, the three fields mentioned are in the database I'm charging, but using the same names and load there is a circular reference.

How can I complete the exercise?

Thanks so much for your answers!!

[#2]

Jonathan,

If i don't use the "Left Join" function, i won't get the result? This is what i'm doing, and it don't work... sorry, i posted the wrong script, there are more tables needed and come traduction from excel...

I post it so you can help me and tell me what i'm doing wrong... after recharge, i get the fiel "INTERVALS", but don't see "Tarifa" anywhere...

Hope you can help me, thanks a lot!!!!

Directory;

LOAD dc_remolcador,

     dg_remolcador,

     [Propietaria RAM],

FROM

[..\..\Admin-Fin\Common data\Common Data.xlsx]

(ooxml, embedded labels, table is [CEBE - Equip]);

Intervals:

LOAD * INLINE [

    TRG_Range,                               From, TO

    0 -> 8000,                               0, 8000

    8001 -> 12000,                     8001, 12000

    12001 -> 25000,           12001, 25000

    25001 -> 999999,           25001, 999999

];

Rules:

LOAD * INLINE [

    TRG_Range, Propietaria_RAM, Puerto, Tarifa

    0 -> 8000, CPT, ANTOFAGASTA, 1000

    8001 -> 12000, CPT, ANTOFAGASTA, 1000

    12001 -> 25000, CPT, ANTOFAGASTA, 1150

    25001 -> 999999, CPT, ANTOFAGASTA, 1400

    0 -> 8000, CPT, CORONEL, 1188

    8001 -> 12000, CPT, CORONEL, 1188

    12001 -> 25000, CPT, CORONEL, 1458

    25001 -> 999999, CPT, CORONEL, 1683

];

Owner:

LOAD "dc_nave",

    "dg_nombre_nave",

    "dq_trg";

SQL SELECT *

FROM "bd_reut".dbo."tb_nave";

[Nave Viaje]:

LOAD "dc_nave_viaje",

    "dc_nave";

SQL SELECT *

FROM "bd_reut".dbo."tb_nave_viaje";

Port:

LOAD "dc_puerto",

    "dg_nombre_puerto";

SQL SELECT *

FROM "bd_reut".dbo."tb_puerto";

[Guía Faena Cabecera]:

LOAD "dn_correlativo_guia",

    "dc_nave_viaje",

    "dc_puerto",

    "dc_remolcador";

SQL SELECT *

FROM "bd_reut".dbo."TB_RAMS_GUIAS_FAENA";

[Guía Faena Detalle]:

LOAD "dn_correlativo_guia",

     "dc_tipo_nave";

SQL SELECT *

FROM "bd_reut".dbo."TB_RAMS_SERV_REMOLCA";

IntervalMatch (dq_trg)

Load [From], [TO]

Resident Intervals;

Drop Table Intervals;

Drop Field [From], [TO];

LOAD * Resident Rules;

DROP Table Rules;

[jonathandienst]

Jose

This is starting to get too complex to resolve abstractly. Please post your model. If it is too large, apply some data reduction to produce a representative subset of the data. If the data as confidential information, you can apply scrambling to randomize the sensitive fields.

Regards

Jonathan

[#4]

Jonathan,

That's the model, the only thing i'm not showing are more fields form each table that i use just as an information field, nothing else...

Hoping this helps you to understand what i'm doing...

Directory;

LOAD dc_remolcador,

     dg_remolcador,

     [Propietaria RAM],                               --> PROPIETARIA RAM

FROM

[..\..\Admin-Fin\Common data\Common Data.xlsx]

(ooxml, embedded labels, table is [CEBE - Equip]);

Thats where i'm getting the "Propietaria_Ram" field, the one specified in Rules.

The rest are the filds form, different tables that are unfied:

Owner:

LOAD "dc_nave",                                    (to load the name from Propietaria_RAM form Excel)

    "dg_nombre_nave",

    "dq_trg";                                              --> TRG_RANGE

SQL SELECT *

FROM "bd_reut".dbo."tb_nave";

[Nave Viaje]:                                             (to make a relation between table Owner and table [Guía Faena Cabecera])

LOAD "dc_nave_viaje",

    "dc_nave";

SQL SELECT *

FROM "bd_reut".dbo."tb_nave_viaje";

Port:

LOAD "dc_puerto",                             

    "dg_nombre_puerto";                         --> PORT (loaded in Inline Rules:)

SQL SELECT *

FROM "bd_reut".dbo."tb_puerto";

[Guía Faena Cabecera]:                          ("Master table" that has detail like dc_puerto -PORT-, dc_remolcador -TO GET THE NAME OF PROPIETARIA_RAM- and  dc_nave_viaje -to get to the table Owner and find the TRG_RANGE-)

LOAD "dn_correlativo_guia",

    "dc_nave_viaje",

    "dc_puerto",

    "dc_remolcador";

SQL SELECT *

FROM "bd_reut".dbo."TB_RAMS_GUIAS_FAENA";

I don't know if that helps, please any doubt just let me know.

Thanks a lot for everything!

[jonathandienst]

Jose

I dont think I can help much more. You need to build a single fact table so that the rules do not cause loops, and I suggest that you join the rules to the fact table, so that you don't have synthetic keys.

That is what I proposed earlier, I am not sure why you don't want the join.

All I can suggest is that you take that proposal as a starting point and extend it to the other information in your data model.

Regards

Jonathan

[#6]

Jonathan,

I load the data base as you told me, this is the script i have and it work, i can recharge it and no problem appears, but i still dont get where am i supposed to find the field "Tarifa" applicated by TRG_Range, Propietaria_RAM and Port.

The model should show my in some part that if i select a a maneuver made with "0- > 8000" TRG_RANGE, "CPT" as Propietaria_RAM and "Antofagasta" Port, 1000 as "Tarifa".

The scritp looks like this:

Intervals:

LOAD * INLINE [

    TRG_Range,                               From, TO

    0 -> 8000,                               0, 8000

    8001 -> 12000,                     8001, 12000

    12001 -> 25000,           12001, 25000

    25001 -> 999999,           25001, 999999

];

Rules:

LOAD * INLINE [

    TRG_Range, Propietaria_RAM, Puerto, Tarifa

    0 -> 8000, CPT, ANTOFAGASTA, 1000

    8001 -> 12000, CPT, ANTOFAGASTA, 1000

    12001 -> 25000, CPT, ANTOFAGASTA, 1150

    25001 -> 999999, CPT, ANTOFAGASTA, 1400

    0 -> 8000, CPT, CORONEL, 1188

    8001 -> 12000, CPT, CORONEL, 1188

    12001 -> 25000, CPT, CORONEL, 1458

    25001 -> 999999, CPT, CORONEL, 1683

];

Owner:

LOAD "dc_nave",

     "dg_nombre_nave",

     "dq_trg";

SQL SELECT *

FROM "bd_reut".dbo."tb_nave";

Left Join (Owner)

LOAD "dc_nave_viaje",

     "dc_nave";

SQL SELECT *

FROM "bd_reut".dbo."tb_nave_viaje";

Left Join (Owner)

LOAD "dc_puerto",

     "dg_nombre_puerto";

SQL SELECT *

FROM "bd_reut".dbo."tb_puerto";

Left Join (Owner)

LOAD "dn_correlativo_guia",

     "dc_nave_viaje",

     "dc_puerto",

     "dc_remolcador";

SQL SELECT *

FROM "bd_reut".dbo."TB_RAMS_GUIAS_FAENA";

Left Join (Owner)

LOAD "dn_correlativo_guia",

     "dc_tipo_nave";

SQL SELECT *

FROM "bd_reut".dbo."TB_RAMS_SERV_REMOLCA";

Left Join (Owner)

LOAD dc_remolcador,

     dg_remolcador,

     [Propietaria RAM],

     D

FROM

[..\..\Admin-Fin\Common data\Common Data.xlsx]

(ooxml, embedded labels, table is [CEBE - Equip]);

   left join (Owner)

IntervalMatch (dq_trg)

Load [From], [TO]

Resident Intervals;

left join (Owner)

Load * Resident Intervals;

Drop Table Intervals;

Drop Field [From], [TO];

LOAD * Resident Rules;

DROP Table Rules;

I only have one table "Owner" with the fields:

TRG_Range

dc_nave

dg_nombre_nave

dq_trg

dc_nave_viaje

dc_puerto

dg_nombre_puerto

dn_correlativo_guia

dc_remolcador

dc_tipo_nave

dg_remolcador

Propietaria RAM

D

The last one, "D", i don't know what is... and why it exists... and as you can see, i can't find the ruls or Tarifa field everywhere...

Hope you can help me... if i can sed you the qvw let me know how!

[jonathandienst]

Jose

D comes from this part of your script:

Left Join (Owner)

LOAD dc_remolcador,

     dg_remolcador,

     [Propietaria RAM],

     D

FROM

[..\..\Admin-Fin\Common data\Common Data.xlsx]

(ooxml, embedded labels, table is [CEBE - Equip]);

Maybe that was a typo?

You omitted the left join in

left join (Owner)

LOAD * Resident Rules;

DROP Table Rules;

Without the join, the load will concatenate onto Rules (creating duplicates), and Rules is then dropped.

Regards

Jonathan

[#8]

Thanks so much Jonathan, it worked!!!

Best regards,

M° José