topic Re: Validate 3 fields to find a value in QlikView

Validate 3 fields to find a value

Tue, 14 Aug 2012 21:18:03 GMT

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

Re: Validate 3 fields to find a value

MayilVahanan — Tue, 14 Aug 2012 21:29:52 GMT

Try something like this,

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

Hope it helps

Re: Validate 3 fields to find a value

Tue, 14 Aug 2012 21:37:50 GMT

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é

Re: Validate 3 fields to find a value

MayilVahanan — Tue, 14 Aug 2012 21:43:41 GMT

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

Re: Validate 3 fields to find a value

Tue, 14 Aug 2012 21:55:46 GMT

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

Re: Validate 3 fields to find a value

Wed, 15 Aug 2012 06:20:23 GMT

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)...

Re: Validate 3 fields to find a value

jonathandienst — Wed, 15 Aug 2012 06:38:49 GMT

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.

Re: Validate 3 fields to find a value

Wed, 15 Aug 2012 15:02:04 GMT

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!

Re: Validate 3 fields to find a value

jonathandienst — Thu, 16 Aug 2012 09:35:19 GMT

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

Re: Validate 3 fields to find a value

Thu, 16 Aug 2012 15:24:40 GMT

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

Regards,

M° José

Re: Validate 3 fields to find a value

Thu, 16 Aug 2012 19:08:27 GMT

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;

Re: Validate 3 fields to find a value

jonathandienst — Fri, 17 Aug 2012 06:00:12 GMT

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

Re: Validate 3 fields to find a value

Fri, 17 Aug 2012 13:00:35 GMT

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!

Re: Validate 3 fields to find a value

jonathandienst — Fri, 17 Aug 2012 13:45:19 GMT

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

Re: Validate 3 fields to find a value

Wed, 22 Aug 2012 19:19:09 GMT

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],

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

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!

Re: Validate 3 fields to find a value

jonathandienst — Thu, 23 Aug 2012 09:07:56 GMT

Jose

D comes from this part of your script:

Left Join (Owner)

LOAD dc_remolcador,

dg_remolcador,

[Propietaria RAM],

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

Re: Validate 3 fields to find a value

Mon, 03 Sep 2012 20:26:56 GMT

Thanks so much Jonathan, it worked!!!

Best regards,

M° José