topic Re: Exists function in QlikView

Exists function

Thu, 18 Feb 2016 10:03:36 GMT

Hello,

i have time dimension that contains date until 2020.

Id like to load only dates that were already loaded in previous tables ( TRANSACTION, VENTE), so in my load intructions i add exists function like this , but once i load data i have 0 lines in my time dimension:

DIM_DATE:

LOAD JULDATE,

JOUR,

"CLE_MOIS",

MOIS,

ANNEE,

SEMAINE,

"CLE_SEMAINE",

DATE1,

TRIMESTRE,

EXERCICE

Where Exists (JULDATE);

SQL SELECT JULDATE,

JOUR,

"CLE_MOIS",

MOIS,

ANNEE,

SEMAINE,

"CLE_SEMAINE",

DATE1,

TRIMESTRE,

EXERCICE

FROM "DM_DEV"."DIM_DATE_FINANCE";

JULDATE is the primary key in my date dimension and foreign key in my fact table.

Re: Exists function

kaushiknsolanki — Thu, 18 Feb 2016 17:26:51 GMT

Try this,

DIM_DATE:

SQL SELECT JULDATE,

JOUR,

"CLE_MOIS",

MOIS,

ANNEE,

SEMAINE,

"CLE_SEMAINE",

DATE1,

TRIMESTRE,

EXERCICE

FROM "DM_DEV"."DIM_DATE_FINANCE";

DIM_DATE_FINAL:

noconcatenate LOAD JULDATE,

JOUR,

"CLE_MOIS",

MOIS,

ANNEE,

SEMAINE,

"CLE_SEMAINE",

DATE1,

TRIMESTRE,

EXERCICE

Resident DIM_DATE

Where Exists (JULDATE);

Drop table DIM_DATE;

Regards,

Kaushik Solanki

Re: Exists function

Peter_Cammaert — Thu, 18 Feb 2016 17:28:49 GMT

Check the field name spelling. Also, does this field JULDATE exist before you perform the DIM_DATE load?

The exists() function will never complain about an non-existent first parameter, but you won't get any data either... Difficult to trace indeed.

Peter

Re: Exists function

Gysbert_Wassenaar — Thu, 18 Feb 2016 17:31:53 GMT

Make sure the JULDATE values actually exist in the tables you loaded earlier. If your dealing with dates then make sure you're dealing with actual dates in all tables and not numeric dates in one table and string values in another.

Re: Exists function

rwunderlich — Thu, 18 Feb 2016 17:43:47 GMT

Are the JULDATE vales in the same format in both tables? If you put them in a listbox and change the format to Number, do the values line up?

-Rob

Re: Exists function

Fri, 19 Feb 2016 11:45:42 GMT

thank you all for your replies, the problem was the qualify* before the definition of my facts tables so i need to add TF_TRANSACTION before the name of the field JULDATE.