7 Replies Latest reply: Aug 16, 2013 6:28 AM by Nikolaos Kavroulakis RSS

    Concatenate fact tables

      Hi Community,

       

      I have 2 fact tables that include exactly the same fields and I am trying to join them. Everything seems fine up until I try to calculate a bit more comple expressions

       

      My fact tables are:

       

      nv200_Data:

      LOAD          TestID,

                    Date(TestDate) as TestDate,

                    TestStatus,

                    if(TestStatus='Pass',1,0) as TestStatusBool,

                    UnitSerial,

                    UnitFirmware,

                    Month(TestDate) as TestMonth,

                    Year(TestDate) as TestYear,

                    Week(TestDate)as TestWeek,

                    Day(TestDate) As TestDay,

                    'Q' &Ceil(Month(TestDate)/3) as Quarter,

                    '1' as PassRateCounter,

                    'NV200' as ValidatorUnit,

                    'Initialisation' as Phase,

                    TestID&'-'&'NV200' as SensorValuesKey;

       

      nv200_Data_2:

      Concatenate(nv200_Data)

      LOAD          TestID,

                    Date(TestDate) as TestDate,

                    TestStatus,

                    if(TestStatus='Pass',1,0) as TestStatusBool,

                    UnitSerial,

                    UnitFirmware,

                    Month(TestDate) as TestMonth,

                    Year(TestDate) as TestYear,

                    Week(TestDate)as TestWeek,

                    Day(TestDate) As TestDay,

                    'Q' &Ceil(Month(TestDate)/3) as Quarter,

                    '1' as PassRateCounter,

                    'NV200' as ValidatorUnit,

                    'Reference' as Phase,

                    TestID&'-'&'NV200' as SensorValuesKey;

       

      Simple expressions like are getting calculated correctly:

      count({$<Phase={'Initialisation'}>}DISTINCT UnitSerial)

      or

      Count({$<TestStatus={'Pass'},Phase={'Initialisation'}>}DISTINCT UnitSerial)

       

      The problem is if I try a more complex one like:

      sum({<Phase={'Initialisation'}>}aggr(TestStatusBool,UnitSerial))

       

      It does not calulate correctly.

      Should I try a different approach no my model?? Or its just a miswritten expression

       

      Regards,