[SCRIPTING] Make a new table

[jumiprado]

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!

[sunny_talwar · Accepted solution]

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;

[jumiprado]

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!

[sunny_talwar]

Not sure what you mean. Can you elaborate?

[jumiprado]

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!

[sunny_talwar]

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;

[jumiprado]

Hey sunindia, i attach an example because i have some warnings when i execute the script!

Can you help me with this?

Thanks You!

[sunny_talwar]

Made some changes. See if this helps