7 Replies Latest reply: Jan 25, 2016 8:25 AM by Sunny Talwar RSS

    [SCRIPTING] Make a new table

    juan prado

      hey Guys! Hello, i need your help to make in my script a new table. I Have a table where a CLIENT_ID could have more than one CLIENT_NAME and for each CLIENT_NAME the STATUS for that CLIENT_NAME in differents MonthYear. The example is something like this:

       

      CLIENT_IDCLIENT_NAMESTATUSMonthYear

      ID_1

      NAME_1

      ACTIVE

      JAN-2015
      ID_1NAME_2ACTIVEJAN-2015
      ID_1NAME_3CANCELEDJAN-2015
      ID_2NAME_4ACTIVE

      JAN-2015

      ID_2NAME_5SUSPENDEDJAN-2015
      ID_1NAME_6ACTIVEFEB-2015
      ID_2NAME_7ACTIVEMAR-2015
      ID_2NAME_4CANCELEDMAR-2015

       

      I have to create a new table like this:

       

      CLIENT_IDMONTHYEAR#_CLIENTS#_CLIENTS_ACTIVE#_CLIENTS_CANCELED
      ID_1JAN-2015321
      ID_2JAN-2015210
      ID_1FEB-2015410
      ID_2MAR-2015211

       

      I need to count per MonthYear the total of clients for each ID, the number of clients where the status is ACTIVE for each ID and the same for CANCELED clients.

       

      Thank you for this help!

        • Re: [SCRIPTING] Make a new table
          Sunny Talwar

          May be this:

           

          Table:

          LOAD CLIENT_ID,

              CLIENT_NAME,

              STATUS,

              MonthYear

          FROM

          [https://community.qlik.com/thread/200921]

          (html, codepage is 1252, embedded labels, table is @1);

           

          [Final Table]:

          LOAD CLIENT_ID,

            MonthYear,

            Count(CLIENT_NAME) as [#_CLIENTS],

            Count(If(STATUS = 'ACTIVE', CLIENT_NAME)) as  [#_CLIENTS_ACTIVE],

            Count(If(STATUS = 'CANCELED', CLIENT_NAME)) as  [#_CLIENTS_CANCELED]

          Resident Table

          Group By CLIENT_ID, MonthYear;

           

          DROP Table Table;

            • Re: [SCRIPTING] Make a new table
              juan prado

              Thanks you so much, if i want to create a MonthYear with every possible value from the min of my field (MonthYear) how can i do it?

               

              Thanks again!

                • Re: [SCRIPTING] Make a new table
                  Sunny Talwar

                  Not sure what you mean. Can you elaborate?

                    • Re: [SCRIPTING] Make a new table
                      juan prado

                      I have this table:

                       

                          

                      CLIENT_IDNAMECLIENT_STATUSDATE_INDATE_OUT
                      800006NAME_3ACTIVE01/02/200100/00/0000
                      800006NAME_8ACTIVE01/02/200100/00/0000
                      800006NAME_15ACTIVE01/02/200100/00/0000
                      800006NAME_19ACTIVE01/02/200100/00/0000
                      800006NAME_5ACTIVE27/05/200200/00/0000
                      800006NAME_9ACTIVE27/05/200200/00/0000
                      800006NAME_7ACTIVE20/12/200400/00/0000
                      800006NAME_14CANCELED20/12/200411/01/2012
                      800006NAME_18CANCELED20/12/200423/09/2013
                      800006NAME_16ACTIVE11/12/200700/00/0000
                      800006NAME_17ACTIVE11/12/200700/00/0000
                      800006NAME_2ACTIVE22/09/200800/00/0000
                      800006NAME_13CANCELED18/02/200903/12/2013
                      800006NAME_6CANCELED11/05/201103/12/2013
                      800006NAME_4CANCELED11/05/201103/12/2013
                      800006NAME_1CANCELED11/05/201128/06/2012
                      800006NAME_11ACTIVE26/05/201400/00/0000
                      800006NAME_10ACTIVE28/04/201500/00/0000
                      800006NAME_12ACTIVE22/06/201500/00/0000

                       

                       

                      and i have to make a new table with the stock from Min (DATE_IN) to TODAY so i need to count the active clients, for example:


                        

                      MonthYearCLIENT_STOCK
                      feb-013
                      mar-013
                      abr-013
                      may-013
                      jun-013
                      jul-013
                      ago-013
                      sep-013
                      oct-013
                      nov-013
                      dic-013
                      ene-023
                      feb-023
                      mar-023
                      abr-023
                      may-025

                       

                       

                      In the table you can see that from feb-01 i have 3 active clients, then in may i have 2 new active clients, so the stock is 5 at that moment, if i have a client with a DATE_OUT different to 00/00/0000 that client is active from the DATE_IN to DATE_OUT.

                      So i have to make a new table with the stock as a new field and a field MonthYear wich is created from the min(DATE_IN) to TODAY.

                       

                      THANKS YOU!