Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Any Help?
Thanks!
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
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.
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;
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
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!
Got it!
The outer Join was the solution,
Thanks for your help,