Exists function

[#1]

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.

[kaushiknsolanki]

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

[Peter_Cammaert]

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

[Gysbert_Wassenaar]

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.

[rwunderlich]

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

[#6]

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.