Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
pgalvezt
Specialist
Specialist

Change Value

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!

7 Replies
pgalvezt
Specialist
Specialist

Any Help?

Thanks!

Not applicable

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

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

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;

Not applicable

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

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

Got it!

The outer Join was the solution,

Thanks for your help,