topic Re: Change Value in QlikView

Change Value

pgalvezt — Mon, 11 Aug 2014 18:58:03 GMT

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!

Re: Change Value

pgalvezt — Mon, 11 Aug 2014 20:34:38 GMT

Any Help?

Thanks!

Re: Change Value

Mon, 11 Aug 2014 20:45:53 GMT

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

Re: Change Value

pgalvezt — Mon, 11 Aug 2014 20:55:10 GMT

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.

Re: Change Value

pgalvezt — Mon, 11 Aug 2014 21:06:24 GMT

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;

Re: Change Value

Tue, 12 Aug 2014 06:13:41 GMT

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

Re: Change Value

pgalvezt — Tue, 12 Aug 2014 12:50:25 GMT

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!

Re: Change Value

pgalvezt — Tue, 12 Aug 2014 15:39:46 GMT

Got it!

The outer Join was the solution,

Thanks for your help,