Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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_ID | CLIENT_NAME | STATUS | MonthYear |
---|---|---|---|
ID_1 | NAME_1 | ACTIVE | JAN-2015 |
ID_1 | NAME_2 | ACTIVE | JAN-2015 |
ID_1 | NAME_3 | CANCELED | JAN-2015 |
ID_2 | NAME_4 | ACTIVE | JAN-2015 |
ID_2 | NAME_5 | SUSPENDED | JAN-2015 |
ID_1 | NAME_6 | ACTIVE | FEB-2015 |
ID_2 | NAME_7 | ACTIVE | MAR-2015 |
ID_2 | NAME_4 | CANCELED | MAR-2015 |
I have to create a new table like this:
CLIENT_ID | MONTHYEAR | #_CLIENTS | #_CLIENTS_ACTIVE | #_CLIENTS_CANCELED |
---|---|---|---|---|
ID_1 | JAN-2015 | 3 | 2 | 1 |
ID_2 | JAN-2015 | 2 | 1 | 0 |
ID_1 | FEB-2015 | 4 | 1 | 0 |
ID_2 | MAR-2015 | 2 | 1 | 1 |
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!
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;
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;
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!
Not sure what you mean. Can you elaborate?
I have this table:
CLIENT_ID | NAME | CLIENT_STATUS | DATE_IN | DATE_OUT |
800006 | NAME_3 | ACTIVE | 01/02/2001 | 00/00/0000 |
800006 | NAME_8 | ACTIVE | 01/02/2001 | 00/00/0000 |
800006 | NAME_15 | ACTIVE | 01/02/2001 | 00/00/0000 |
800006 | NAME_19 | ACTIVE | 01/02/2001 | 00/00/0000 |
800006 | NAME_5 | ACTIVE | 27/05/2002 | 00/00/0000 |
800006 | NAME_9 | ACTIVE | 27/05/2002 | 00/00/0000 |
800006 | NAME_7 | ACTIVE | 20/12/2004 | 00/00/0000 |
800006 | NAME_14 | CANCELED | 20/12/2004 | 11/01/2012 |
800006 | NAME_18 | CANCELED | 20/12/2004 | 23/09/2013 |
800006 | NAME_16 | ACTIVE | 11/12/2007 | 00/00/0000 |
800006 | NAME_17 | ACTIVE | 11/12/2007 | 00/00/0000 |
800006 | NAME_2 | ACTIVE | 22/09/2008 | 00/00/0000 |
800006 | NAME_13 | CANCELED | 18/02/2009 | 03/12/2013 |
800006 | NAME_6 | CANCELED | 11/05/2011 | 03/12/2013 |
800006 | NAME_4 | CANCELED | 11/05/2011 | 03/12/2013 |
800006 | NAME_1 | CANCELED | 11/05/2011 | 28/06/2012 |
800006 | NAME_11 | ACTIVE | 26/05/2014 | 00/00/0000 |
800006 | NAME_10 | ACTIVE | 28/04/2015 | 00/00/0000 |
800006 | NAME_12 | ACTIVE | 22/06/2015 | 00/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:
MonthYear | CLIENT_STOCK |
feb-01 | 3 |
mar-01 | 3 |
abr-01 | 3 |
may-01 | 3 |
jun-01 | 3 |
jul-01 | 3 |
ago-01 | 3 |
sep-01 | 3 |
oct-01 | 3 |
nov-01 | 3 |
dic-01 | 3 |
ene-02 | 3 |
feb-02 | 3 |
mar-02 | 3 |
abr-02 | 3 |
may-02 | 5 |
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!
Try the following script:
Table:
LOAD CLIENT_ID,
NAME,
CLIENT_STATUS,
DATE_IN,
DATE_OUT
FROM
[https://community.qlik.com/thread/200921]
(html, codepage is 1252, embedded labels, table is @3);
MinMax:
LOAD Min(DATE_IN) as Min,
If(Max(DATE_OUT) > Max(DATE_IN), Max(DATE_OUT), Max(DATE_IN)) as Max
Resident Table;
LET vMin = Peek('Min');
LET vMax = Peek('Max');
DROP Table MinMax;
Table2:
NoConcatenate
LOAD CLIENT_ID,
NAME,
CLIENT_STATUS,
DATE_IN,
Date(If(DATE_OUT = '00/00/0000', $(vMax), DATE_OUT)) as DATE_OUT
Resident Table;
DROP Table Table;
Calendar:
LOAD Date,
MonthName(Date) as MonthYear;
LOAD Date($(vMin) + IterNo() - 1) as Date
AutoGenerate 1
While ($(vMin) + IterNo() - 1) <= $(vMax);
IntervalMatch:
IntervalMatch (Date)
LOAD DATE_IN,
DATE_OUT
Resident Table2;
Hey sunindia, i attach an example because i have some warnings when i execute the script!
Can you help me with this?
Thanks You!
Made some changes. See if this helps