topic Re: script error in QlikView

script error

Thu, 13 Mar 2014 17:17:29 GMT

Hello,

I have this script :

LOAD hash128(Date_analyse, Code_magasin, Code_article) as Key_pivot

, hash128(Date_analyse, Code_magasin) as Key_increment

, *;

LOAD DISTINCT

date#(Date_vte, 'DD/MM/YYYY') as Date_analyse

, Code_magasin as Code_magasin

, Code_article as Code_article

, Qte_vendue as Qte_vendue_vente

, Montant_achat as Montant_achat_vente

, Montant_vte as Montant_vente

, PRX_PERM as Prix_permanent_vente

, Code_TVA as Code_tva_vente

, Mesure as Mesure_vente

FROM

$(vCSV)

(txt, codepage is 1252, embedded labels, delimiter is ';', msq);

LEFT JOIN ($(vNomQVD_ventes))

LOAD code_tva as Code_tva_vente

, taux_tva as Taux_tva_vente

, date#(date_tva, 'DD/MM/YYYY') as Date_tva_vente

, date#(date_fin, 'DD/MM/YYYY') as Date_fin_tva_vente

FROM

$(vNomCSV_ventes_2).csv

(txt, codepage is 1252, embedded labels, delimiter is ';', msq)

WHERE Date#(Date_vte, 'DD/MM/YYYY') >= Date#(date_tva, 'DD/MM/YYYY') AND date#(Date_vte, 'DD/MM/YYYY') <= date#(date_fin, 'DD/MM/YYYY');

The script generate this error.

Error: Field not found - <Date_vte>

Someone can help me ?

Thanks a lot.

Re: script error

Gysbert_Wassenaar — Thu, 13 Mar 2014 17:33:32 GMT

One of your csv files seems to be missing the Date_vte field. Make sure you're using the exact case-sensitive field names. A field named date_vte is a different field to Qlikview than a field named Date_vte.

Also, just in case that's what you're trying, you cannot use a field from another table in the where clause in a comparison. For WHERE Date#(Date_vte, 'DD/MM/YYYY') >= Date#(date_tva, 'DD/MM/YYYY') to work both Date_vte and date_tva need to exist in the csv file $(vNomCSV_ventes_2).csv

Re: script error

maxgro — Thu, 13 Mar 2014 17:33:58 GMT

does it exists Date_vte column in your files?

could you post your 2 files?

Re: script error

giakoum — Thu, 13 Mar 2014 17:36:03 GMT

it is missing in one of the 2 loads.

try commenting out 1 load per time to see from which one it is missing.

I guess $(vCSV) and $(vNomCSV_ventes_2).csv are 2 different files, right?

Re: script error

Thu, 13 Mar 2014 18:09:55 GMT

Hi,

Thanks for your answer.

I have no problem with this LOAD without WHERE clause.

It's true, Date_vte not exist in the csv file $(vNomCSV_ventes_2).csv just in the first file.

I just want to compare the field Date_vte on the firest file with the 2 date field for the second file.

Re: script error

Thu, 13 Mar 2014 18:11:43 GMT

True, Date_vte don't exists in the first file...

First file :

Code_magasin;Date_vte;Code_article;Qte_vendue;Montant_achat;Montant_vte;PRX_PERM;Code_TVA;Mesure

Second file :

code_tva;taux_tva;date_tva;date_fin

Thanks to you

Re: script error

Thu, 13 Mar 2014 18:13:21 GMT

It's true, the two files are different.

First file :

Code_magasin;Date_vte;Code_article;Qte_vendue;Montant_achat;Montant_vte;PRX_PERM;Code_TVA;Mesure

Second file :

code_tva;taux_tva;date_tva;date_fin

Thanks

Re: script error

giakoum — Thu, 13 Mar 2014 18:13:24 GMT

ok but which value of date_vte should the comparison take? Min, Max? It can compare each line with all values, no this way at least. What are you trying to achieve?

Re: script error

Thu, 13 Mar 2014 20:05:47 GMT

Hello all,

Thanks a lot for your answer.

Finally, I managed to load my data. I proceeded differently:
- I first loaded the data of my first file into a temporary table.
- Then I loaded the data of my second file in another temporary table by making a "LEFT JOIN" with my first temporary table.
- I finally filtered my data (WHERE CLAUSE) with my second temporary table.