Qlik Community

Groupe des Utilisateurs Francophones

Vous êtes francophone? Ce groupe est pour vous. Venez découvrir comment démarrer avec Qlik Sense et QlikView, poser vos questions et partager vos tutos et astuces avec les membres de notre communauté.

Highlighted
Not applicable

field calculated from two tables or more

Hi,

I would like to créate a news columns from 2 tables:

CASE WHEN (D.DOC_STYPE) IN ('A', '0', 'R') THEN -L.LIG_QTE ELSE L.LIG_QTE END AS 'Qte',

CASE WHEN (D.DOC_STYPE) IN ('A', '0', 'R') THEN -(L.[LIG_QTE] * L.LIG_POIDSN) ELSE L.[LIG_QTE] * L.LIG_POIDSN END AS 'Poids',

Is this code is correct?

DIM_DOCUMENTS :

LOAD     Month (DOC_DATE) as Mois,

        Year ("DOC_DATE") as Année,

        date ("DOC_DATE") as Date,

        "DOC_TYPE",

        "DOC_STYPE",

        If(DOC_TYPE = 'A', 'Achat', if(DOC_TYPE = 'V', 'Ventes')) as [Type de doc.],

/*Sous types de documents*/

        If(DOC_TYPE = 'V',

          if(DOC_STYPE = 'P', 'Pro-forma',

          if(DOC_STYPE = 'D', 'Devis',

          if(DOC_STYPE = 'C', 'Commandes',

          if(DOC_STYPE = 'B', 'Bons de Livraison',

          if(DOC_STYPE = 'R', 'Bons de Retour',

          if(DOC_STYPE = 'F', 'Factures',

          if(DOC_STYPE = '1', 'Factures Financières',

          if(DOC_STYPE = 'A', 'Avoirs',

          if(DOC_STYPE = '0', 'Avoirs Financiers',

          ))))))))),

          if(DOC_TYPE =  'A',

          if(DOC_STYPE = 'D', 'Demandes de Prix',

          if(DOC_STYPE = 'C', 'Commandes',

          if(DOC_STYPE = 'B', 'Bons de Réceptions',

          if(DOC_STYPE = 'R', 'Bons de Retour',

          if(DOC_STYPE = 'F', 'Factures',

          if(DOC_STYPE = '1', 'Factures Financières',

          if(DOC_STYPE = 'A', 'Avoirs',

          if(DOC_STYPE = '0', 'Avoirs Financiers')

          ))))))))

          ) as [Sous type de doc.],

        "DEP_CODE",

        "DEV_CODE",

        "DEV_CODE",

        "DIV_CODE",

        "DOC_DT_PRV",

        "DOC_EN_TTC",

        "DOC_ETAT",

        "DOC_NUMERO";

SQL SELECT     DOC_DATE,

            '[Sous type de doc.]',

            DEP_CODE,

            DEV_CODE,

            DEV_CODE,

            DIV_CODE,

            DOC_DT_PRV,

            DOC_EN_TTC,

            DOC_ETAT,

            DOC_NUMERO

FROM ICP.dbo.DOCUMENTS;

Join Load

     DOC_NUMERO,

     LIG_QTE

From ICP.dbo.LIGNES

Result:

LOAD *,

If(Match(DOC_STYPE ,'A', '0', 'R'),-(LIG_QTE),LIG_QTE) as 'Qte',

If(Match(DOC_STYPE ,'A', '0', 'R'),-(LIG_QTE * L.LIG_POIDSN),(LIG_QTE * L.LIG_POIDSN)) as 'Poids',

If(Match(DOC_STYPE ,'A', '0', 'R'),-(LIG_FRAIS * LIG_QTE),LIG_FRAIS * LIG_QTE) as 'Frais1'

Resident ;

drop table DIM_DOCUMENTS;

regards