Change Value

[pgalvezt]

Hello,

I have many tables into my app. One of them is called "Cosumo" Calculated Fields. Here you will find codigo and fecha_precep, Until here my app works fine. But now I have to change the status of the code. You will find the new codes into the Excel test. I need to change that date "04-08-2014" if codigo (Consumo) = Codigo2 then FechaPrueba (Into Excel).

For example L010-OT-00T22337CP has Status "Inmovilizado" if a change the date value Status will change to "Compras < 1 Año" Actually this status is: "Inmovilizado" The others codes no change.

Thanks!

[pgalvezt]

Any Help?

Thanks!

[#3]

HI,

I'm not sure that I understand correctly the question but I'll try to help you. So you can try with something like that:

IF( codigo (Consumo) = Codigo2 ,  FechaPrueb = .........  ,

     IF('Compras' < '1 Año" Actually', Status = 'Inmovilizado', Status = ..........) )

Regards,

Venelin

[pgalvezt]

Hi Venelin,

Thank you for your reply

The logic is right, I need to do another thing.

In excel I have Code and dates. I need cross the excel file with Consumo Table. so if the code in excel = Code Consumo then change the value in consumo table (my formula do the rest (Change the Status)) I just need to change the dates of these tables. I try with left join but doesnt work.

[pgalvezt]

I forgot to say that I need to do the change into the script, because the formulas are fine. I add a table resident but no take the changes. Test_Nueva_Fecha is the excel file. Test_Consumo is the table from database.

Test_Consumo:

LOAD

  Upper([inv-part                ]) as Codigo,

  [inv-site                ] as Almacen_Consumo,

  [inv-loc                ],

  [inv-consumoso12        ],

  [inv-consumowo12        ],

  - [inv-consumoso12        ] - [inv-consumowo12        ]  as Consumo12,

  [inv-consumoso11        ],

  [inv-consumowo11        ],

  - [inv-consumoso11        ] - [inv-consumowo11        ]  as Consumo11,

  [inv-consumoso10        ],

  [inv-consumowo10        ],

  - [inv-consumoso10        ] - [inv-consumowo10        ]  as Consumo10,

  If(date#([inv-fping                ],'DD/MM/YY')='?','0',date#([inv-fping                ],'DD/MM/YY')) as fecha_precep1

FROM datos\mov.txt (ansi, txt, delimiter is ';', embedded labels, msq);

Test_Nueva_Fecha:

Left Join (Test_Consumo)

LOAD

  Upper(Codigo2)          as Codigo2,

    Date([Fecha Prueba])  as Fecha_Prueba

FROM datos\Nueva_Fecha_Provisión.xls (biff, embedded labels, table is Sheet1$);

Consumo:

LOAD

  Upper(Codigo) as Codigo,

  Codigo2,

  Fecha_Prueba,

  Almacen_Consumo,

  [inv-loc                ],

  [inv-consumoso12        ],

  [inv-consumowo12        ],

  Consumo12,

  [inv-consumoso11        ],

  [inv-consumowo11        ],

  Consumo11,

  [inv-consumoso10        ],

  [inv-consumowo10        ],

  Consumo10,

  //If(Codigo=Codigo,Fecha_Prueba,fecha_precep1) as fecha_precep

  If(Codigo2=Codigo,Fecha_Prueba,If(date#(fecha_precep1,'DD/MM/YY')='?','0',date#(fecha_precep1,'DD/MM/YY'))) as fecha_precep

Resident Test_Consumo;

Drop Table Test_Consumo;

[#6]

Ok.

You can link the table with the field with name Codigo.

If you use Inner Join you will have unique values, but you'll have problem with the missing data.

If you use Concatenate function you'll have all informations in database, but it's possible to have repeat records in Codigo field.

So my advice is to use AutoNumber Hash function first to create unique value field and after that to concatenate the tables. Try with some Date field and Codigo field or something like that.

Most important is to have unique value field and next to link the tables with this field.

Regards,

Venelin

[pgalvezt]

Hi,

"You can link the table with the field with name Codigo." Do you mean in the excel file Codigo2, call it Codigo?

If so, how can I use the logic "If"?

If(Codigo2=Codigo,Fecha_Prueba,If(date#(fecha_precep1,'DD/MM/YY')='?','0',date#(fecha_precep1,'DD/MM/YY'))) as fecha_precep

If, So can you please send me an example with my app that I attached.

Thanks!

[pgalvezt]

Got it!

The outer Join was the solution,

Thanks for your help,