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!!

[MayilVahanan]

HI

     Try something like this,

     =Sum({<Owner={'RAM'},Port={'Antofagasta'},TRG_Range={">0<8000"},Tarifa={">800<1000"}>}Fees)

Hope it helps

[#3]

Mayil,

Thanks for your answer. The thing is that the excel table that i posted is just a small part of it, i have at least 15 TRG_Ranges and 14 different ports, so i need a solution that can apply for more data than posted.

Thanks a lot, hope you could still help me.

M° José

[MayilVahanan]

Hi

     For any case,it satisfy your requirement..Can you say clearly in it

[#5]

Is there any other solution in wich i don't have to set all the ports, and all the TRG ranges in the formula?

I guess maintain a large formula like that isn't I the optimal for this case...

Thanks for your help!!!

[#6]

i guess you can either manually set the array variable or you can load those variables from your file

set vPort = ''Antofagasta','Antofagasta2'... 

=Sum({<Owner=$(vOwner),Port=$(vPort)...

[jonathandienst]

Jodelgado,

So you mean that you want to select the value of Tarifa based on TRG_Range, Owner and Port?

Is this in a table/chart; and what are the dimenions in the chart? or in script? And how do you want to apply the fee - I assume it is multiplied by or added to some amount.

Is TRG_Range a value in your data source, or do you have another field (eg TRG) that must fit in the TRG_Range?

In this case it might be an idea to post a sample of your model and data file(s).

Regards

Jonathan

BTW - your English is fine.

[#8]

Jonathan,

I'm charging from my DB some filds, like TRG, Port and Owner, so the script looks like this.

LOAD "dc_puerto",

    "dg_nombre_puerto", --> PORT

SQL SELECT *

FROM "bd_reut".dbo."tb_puerto" tb_puerto;

LOAD "dc_nave",

    "dg_nombre_nave",

    "dq_trg", --> TRG

SQL SELECT *

FROM "bd_reut".dbo."tb_nave" tb_nave;

LOAD

    "dc_puerto",

    "dc_nave",

    "dc_remolcador", --> OWNER

SQL SELECT *

FROM "bd_reut".dbo."TB_RAMS_GUIAS_FAENA" TB_RAMS_GUIAS_FAENA;

STORE TB_RAMS_GUIAS_FAENA INTO RAMS_FAENA_OPS.QVD;

I made TRG Range inserting Inline in the script.

The other part is the excel, that has the structure i posted:

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

What i need is to make a table chart, that indicates me: If the TRG Range is between 0 and 8000, the owner is Stack and the port is Antofagasta, show me in a new column the value 800. If the TRG Range is between 0 and 8000, the owner is Tickels and the port is Antofagasta, show me 1000. So, i have to find some way to find the "tarifa" if the TRG is "X", the Owenr "Y" and the port "Z".

The thing is that the excel table has like 15 TRG Ranges, 14 Ports and two clintes...

Hope you can help me!!! Thanks for understand my english... hahahahaha!

[jonathandienst · Accepted solution]

Jodelgado

I suggest the following script:

// Intervals definitions for TRG_Range 
Intervals:
LOAD * Inline
[
          TRG_Range, From, To
          0->8000, 0, 8000
        8000->10000, 8001, 10000
          ... add others here ...
          >20000, 20001,
];
// Tariff rules per your structure
Rules:
LOAD * Inline
[
          TRG_Range, dc_remolcador, puerto, Tarifa
          0->8000, STACK, ANTOFAGASTO, 800
          0->8000, TICKELS, ANTOFAGASTO, 1000
        8000->10000, NAME1, ANTOFAGASTO, 600
          ... add others here ...
];
// Combine the loads to produce a single fact table
Owner:
LOAD
    "dc_puerto",
    "dc_nave",
    "dc_remolcador", --> OWNER
SQL SELECT *
FROM "bd_reut".dbo."TB_RAMS_GUIAS_FAENA";
Left Join (Owner)
LOAD "dc_puerto",
    "dg_nombre_puerto", --> PORT
SQL SELECT *
FROM "bd_reut".dbo."tb_puerto";
Left Join (Owner)
LOAD "dc_nave",
    "dg_nombre_nave",
    "dq_trg", --> TRG
SQL SELECT *
FROM "bd_reut".dbo."tb_nave"; 
// Match to get the TRG_Range interval to which the TRG belongs
// IntervalMatch adds the From and To fields to Nave
Left Join (Owner)
IntervalMatch (dq_trg)
LOAD [From], 
          [To]
Resident Intervals;
// Optional cleanup to avoid unnecessary table and syn key
// This adds the TRG_Range to Nave
Left Join (Owner)
LOAD * Resident Intervals;
Drop Table Intervals;
Drop Fields [From], [To];
// Now add the field [Tarifa] to the fact table
Left Join (Owner)
LOAD * Resident Rules;
// Need to drop this to prevent unnecessary syn key
DROP Table Rules;

Populate the Interval table with the other TRG_Range values you need, and populate the Rules table with additional rules. The values of TRG_Range must be the same (spelling) in both tables.

I have combined all the tables into a single fact table so that you can join the rules in to get the Tarifa value into the fact table as well. Sorry about the mix of languages in the script, you can correct this once it is working.

Regards

Jonathan

[#10]

Thanks for your answer Jonathan, i'll try and let you know if it worked!

Regards,

M° José