2 Replies Latest reply: Jul 5, 2017 2:05 PM by Bala Bhaskar RSS

    new calculated field between 2 tables

    ibrahibra aljefri

      hi

      i am using qlik sense desktop and i want to know what is the proper logic to create new calculated field and the aggregation contain a field from table 1 and another filed from table 2 to be created in load editor either in table 1 load or table 2 

        • Re: new calculated field between 2 tables
          Daniel Berkes

          You can use such a code like this:

           

          Table1:

          LOAD * INLINE [

              Dimension, MeasureA

              Dim1, 10

              Dim2, 20

              Dim3, 5

          ];

           

           

          Table2:

          left Join(Table1)

          LOAD  *  INLINE [

              Dimension, MeasureB

              Dim1, 12

              Dim2, 23

              Dim3, 4

          ];

           

           

          Table3:

          LOAD

          *,

          MeasureB  *  MeasureA as MeasureC

          Resident Table1;

           

           

          DROP Table Table1;

           

          But you can make expression in the front end for example in the List Box object.

           

          Basic principle is do every calculation in the backend if it is possible.

           

          Daniel

          • Re: new calculated field between 2 tables
            Bala Bhaskar

            Try this:

            TempBudgetUSA:

            CrossTable(DocumentDate, Amount, 3)

            LOAD Sale,

            Include,

            [Account No],

            FROM [C:\abc\ab\*.xlsx]

            (ooxml, embedded labels, table is Sheet1) where Include = 'y';

             

            left join (TempBudgetUSA)

             

            TempBudgetUK:

            CrossTable(DocumentDate, Amount, 3)

            LOAD Sale,

            Include,

            [Account No],

            FROM [C:\abc\cd\*.xlsx]

            (ooxml, embedded labels, table is Sheet1) where Include = 'y';

             

            Final:

            Noconcatenate

            Load

            Max(Include) as MaxIncl,

            Sum(Sale) as SumSale

            Resident TempBudgetUSA

            Group by Sale,  Include,  [Account No];

             

            Drop table TempBudgetUSA;