3 Replies Latest reply: Jan 4, 2017 4:30 AM by Siddharth Sharma RSS

    Create 3 subsets (table from Original Table)

    Siddharth Sharma

      Hi Team,

       

      Can you please guide me How can I create 3 subsets (tables using Where Clause) from parent table...

      I am finding difficulty in using Where clause

       

      Say I have a table named Original

      Say it has a field named Status and Say Status has Value like Unresolved, Resolved and Delete

       

      How I can create 3 subsets of tables , Table 1 where Status ='Resolved'

      Table 2 where Status ='Unresolved'

      Table 3 where Status ='Deleted


      I have my Script for Original table as

      Original:

      LOAD Id, FileDate,

                      Status, 

      FROM

      [C:\Users\s198\Desktop\Sample\TestFile.xlsx]

      (ooxml, embedded labels);

       

      Regards

      Siddharth

        • Re: Create 3 subsets (table from Original Table)
          Manish Kachhia

          Original:

          LOAD Id, FileDate,

                          Status,

          FROM

          [C:\Users\s198\Desktop\Sample\TestFile.xlsx]

          (ooxml, embedded labels);

           

          NoConcatenate

          T1:
          Load * Resident Original Where Status = 'Resolved';

          Store T1 into T1.qvd(QVD);

           

          NoConcatenate

          T2:
          Load * Resident Original Where Status = 'Unresolved';

          Store T2 into T2.qvd(QVD);

           

          NoConcatenate

          T2:
          Load * Resident Original Where Status = 'Deleted';

          Store T3 into T3.qvd(QVD);

           

          Drop Table Original;

           

          ===============================

           

          I am assuming that you want to create 3 different QVDs.

          If you don't want to do this... what is the reason you want to have three different tables with different status?

            • Re: Create 3 subsets (table from Original Table)
              Siddharth Sharma

              Thanks Manish,

               

              Yes you are right, I was thinking of joining them so that I can have Date open(from Table 2) and Date Close(Table) in One New Table, and then May be it is easier for me to calculate Cycle Time

               

              Can you guide me with better alternate, as there are Ids can be in Unresolved status for may dates..eg

              Id Error Date             Status

              A   1     12/1/2016      Unreolved

              A   1      12/2/2016     Unresolved

              A    1      12/3/2016     Unresolved

              A   1      12/5/2016       Resolved

              and I need to find average Cycle time, Best part is I also need to create aging(if Status remain Open )

               

               

              Regards

              Siddharth

              • Re: Create 3 subsets (table from Original Table)
                Siddharth Sharma

                Hi Manish,

                 

                When I am trying to draw table from T1, Due to synthetic Keys, I am getting all the entries apart from Resolved status.

                 

                I was thinking of using a complete join between Table T 1 and T2

                 

                Regards

                Siddharth