10 Replies Latest reply: Nov 16, 2015 12:44 PM by Anselmo Meira RSS

    Problem with JOIN or Crosstable

    Anselmo Meira

      Hi all,

       

      I am experiencing a problem using a join or crosstable. A numeric value is set to -1 automatically where it was about to show another value instead.

       

      Does anyone had the same problem? How can I fix that? I would like to sum all these values instead of '-1' and I am getting the wrong result.

       

      Below is the table

        

      MTX TYPEUS$
      Handling-1
      Tyre-1
      Other-1
      Handling

      -1

       

       

      Thank you!

        • Re: Problem with JOIN or Crosstable
          Mark Little

          Hi,

           

          Is it possible to get some more information. I.e. some sample data, your script or little QVW?

           

          Mark

          • Re: Problem with JOIN or Crosstable
            Anselmo Meira

            In addition to the question, it is not all values that is showing '-1'. There are some showing it's real value.

             

            Thanks

            • Re: Problem with JOIN or Crosstable
              Sunny Talwar

              Would you be able to share a sample to see what exactly is going on?

              • Re: Problem with JOIN or Crosstable
                Vincent Ardiet

                Hi

                Could you post a sample of your code and eventually a load inline with few lines of data producing this strange effect?

                • Re: Problem with JOIN or Crosstable
                  Anselmo Meira

                  Yes, I think the problem can be in the load script.

                   

                  Gonna format it in a proper way an then I post the code here.

                  Thank you!

                  • Re: Problem with JOIN or Crosstable
                    Anselmo Meira

                    Folks,


                    Below is the load script.


                    Cars:
                    LOAD Model__c,
                    [S/N],
                    MTX_START,
                    REGION,
                    SUBREGION
                    FROM
                    AAA
                    (
                    ooxml, embedded labels);

                    join(Cars)
                    LOAD Model__c,
                    [S/N],
                    [TEST Type],
                    [TEST Start Date],
                    [TEST End Date]

                    FROM
                    BBB
                    (
                    ooxml, embedded labels, table is [Enrollment]);

                    join (Cars)
                    LOAD Model__c,
                    TIMEFRAME,             
                    num(Handling, '#.##0,00') as  [Handling],
                    num(Tyre, '#.##0,00') as Tyre,
                    num(Other, '#.##0,00') as Other

                    FROM
                    CCC
                    (
                    ooxml, embedded labels, table is [TT FINAL]);

                    TableA:
                    load

                         Model__c,
                    [S/N],
                    MTX_START,
                    REGION,
                    SUBREGION,
                    [TEST Type],
                    [TEST Start Date],
                    [TEST End Date],

                     
                    //Maintenance Date Calculation

                         Date(MonthName(TIMEFRAME + MTX_START), 'MM/YYYY') as [MTX Date],

                    Handling,

                    Tyre,

                    Other,    

                    // TEST Vingent calculation
                          
                    if(Date(MonthName(TIMEFRAME + MTX_START), 'MM/YYYY')>=Date(MonthName([TEST Start Date]), 'MM/YYYY') and Date(MonthName(TIMEFRAME + MTX_START), 'MM/YYYY')<=Date(MonthName([TEST End Date]), 'MM/YYYY'),
                    if([TEST Type]='STD', num(Tyre + Other + Handling,'#.##0,00'),0),0) as [TEST STD]

                    Resident Cars;
                    drop table Cars;

                    //removing Duplicated Values    
                    Conditional:
                    load
                    Model__c,
                    [S/N],
                    MTX_START,
                    REGION,
                    SUBREGION,
                    [TEST Type],
                    [TEST Start Date],
                    [TEST End Date],
                    TIMEFRAME,
                    SCHEDULED_MTX,
                    MTX_MONTH as [MTX Type],


                    //Separate calculations for chart
                    [Handling] as [ Handling Chart],
                    [Tyre] as [Tyre Chart],
                    [Other] as [Other Chart],
                     
                    //Conditional for removing duplicated values
                            if([MTX Date]>=Date(MonthName([TEST Start Date]), 'MM/YYYY') and [MTX Date]<=Date(MonthName([TEST End Date]), 'MM/YYYY'),
                    if([TEST Type]='STD', [Handling]=0),
                    [Handling]as  [Handling],

                    if([MTX Date]>=Date(MonthName([TEST Start Date]), 'MM/YYYY') and [MTX Date]<=Date(MonthName([TEST End Date]), 'MM/YYYY'),
                    if([TEST Type]='STD', [Tyre]=0),
                    [Tyre]) as [Tyre],

                    if([MTX Date]>=Date(MonthName([TEST Start Date]), 'MM/YYYY') and [MTX Date]<=Date(MonthName([TEST End Date]), 'MM/YYYY'),
                    if([TEST Type]='STD', [Other]=0),
                    [Other]) as [Other]     

                     
                    resident TableA;
                    drop Table TableA;


                      FinalTable:
                    CrossTable ([MTX Type], [US$],11)    

                    load *
                    Resident Conditional;
                    DROP Table Conditional;



                    Thanks

                    • Re: Problem with JOIN or Crosstable
                      Anselmo Meira

                      Almost finding the solution.

                       

                      The error is in this Step:

                              if([MTX Date]>=Date(MonthName([TEST Start Date]), 'MM/YYYY') and [MTX Date]<=Date(MonthName([TEST End Date]), 'MM/YYYY'),
                      if([TEST Type]='STD', [Handling]=0),
                      [Handling]as  [Handling],


                      Where it sets -1 to all zeros values. I am looking for non zeros values in order to see if it is replacing any value I want.


                      I will keep this post update as I am debugging the code.


                      Thanks

                        • Re: Problem with JOIN or Crosstable
                          Sunny Talwar

                          May be this:

                           

                          if([MTX Date]>=Date(MonthName([TEST Start Date]), 'MM/YYYY') and [MTX Date]<=Date(MonthName([TEST End Date]), 'MM/YYYY'),
                          if([TEST Type]='STD', 0),
                          [Handling]as  [Handling]
                          ,


                          or


                          if([MTX Date]>=Date(MonthName([TEST Start Date]), 'MM/YYYY') and [MTX Date]<=Date(MonthName([TEST End Date]), 'MM/YYYY'),
                          if([TEST Type]='STD', 0, [Handling]),
                          [Handling]as  [Handling]
                          ,

                        • Re: Problem with JOIN or Crosstable
                          Anselmo Meira

                          Hi Sunny.

                          Just tested and it doesn't work.


                          The solution is below.

                          if([MTX Date]>=Date(MonthName([TEST Start Date]), 'MM/YYYY') and [MTX Date]<=Date(MonthName([TEST End Date]), 'MM/YYYY'),
                          if([TEST Type]='STD',

                          if(Handling='0', Handling, Handling='0')),

                          [Handling]as  [Handling],