10 Replies Latest reply: Jan 4, 2018 8:10 PM by Cameron Vine RSS

    Cross table with four attributes

    Cameron Vine

      Hi

       

      I need to normalise a  table and have read a number of the communities posts but they are not quite the same as my data table.

       

      I know that I must do multiple steps but I am stuck after the initial cross table script. I lose dimensions and then cannot get to the next steps.

       

      Once I have the fields loaded I then need to do some transformations using if statements that I have already compiled and loop through the other files in the directory to consume these as well.

       

      Attached is my data file, how it should look (example from and to) and my attempt at the Load data manually script in the qvf file.

       

      Cheers

       

      Cameron

        • Re: Cross table with four attributes
          Petter Skjolden

          You don't need a CROSSTABLE prefix to achieve this - it is simpler to just load the table twice from a resident raw table:

           

          RAW_TABLE:
          LOAD 
            *
            FROM
          [MileStone_Breach_Empty_Details_Data.csv]
          (txt, codepage is 1252, embedded labels, delimiter is ',', msq);
          
          
          DATA:
          LOAD 
            BusinessDate, 
            Milestone, 
            Report, 
            Portfolio, 
            FeedId, 
            [Source SLA] AS sla, 
            [Source Start] AS [Time Start], 
            [Source End] AS [Time End], 
            [Source Status] AS [Minutes], 
            'Source' AS System
          RESIDENT
            RAW_TABLE;
          
          
          LOAD 
            BusinessDate, 
            Milestone, 
            Report, 
            Portfolio, 
            FeedId, 
            [MaRRS SLA] AS sla, 
            [MaRRS Start] AS [Time Start], 
            [MaRRS End] AS [Time End], 
            [MaRRS Status] AS [Minutes],
            'Marrs' AS System
          RESIDENT
            RAW_TABLE;
          
          
          DROP TABLE RAW_TABLE;
          
          • Re: Cross table with four attributes
            Petter Skjolden

            Or if you hate to load the data twice after a resident load you could simply use WHILE to produce two rows out per row in:

             

            DATA:
            LOAD 
              BusinessDate, 
              Milestone, 
              Report, 
              Portfolio, 
              FeedId, 
              If( IterNo() = 1 , [Source SLA] , [MaRRS SLA] ) AS sla,
              If( IterNo() = 1 , [Source Start] , [MaRRS Start] ) AS [Time Start],
              If( IterNo() = 1 , [Source End] , [MaRRS End] ) AS [Time End], 
              If( IterNo() = 1 , [Source Status] , [MaRRS Status] ) AS [Minutes],
              If( IterNo() = 1 , 'Source' , 'Marrs' ) AS System
            FROM
              [MileStone_Breach_Empty_Details_Data.csv]
              (txt, codepage is 1252, embedded labels, delimiter is ',', msq)
            WHILE
              IterNo()<=2;
            
            • Re: Cross table with four attributes
              Petter Skjolden

              If you want to refer to the columns not by name but by number you could do this:

               

              DATA:
              LOAD
                @1 AS BusinessDate,
                @2 AS Milestone,
                @3 AS Report,
                @4 AS Portfolio,
                @5 AS FeedId,
                @6 AS test,
                Pick( IterNo() , @6  , @10 ) AS sla,
                Pick( IterNo() , @7  , @11 ) AS [Time Start],
                Pick( IterNo() , @8  , @12 ) AS [Time End],
                Pick( IterNo() , @9  , @13 ) AS [Minutes],
                Pick( IterNo() , 'Source' , 'Marrs' ) AS System
              FROM
                [MileStone_Breach_Empty_Details_Data.csv]
                (txt, codepage is 1252, no labels,Header is 1 line, delimiter is ',', msq)
              WHILE
                IterNo()<=2;