Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
jumiprado
New Contributor III

[SCRIPTING] Make a new table

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!

1 Solution

Accepted Solutions

Re: [SCRIPTING] Make a new 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;

7 Replies

Re: [SCRIPTING] Make a new 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;

jumiprado
New Contributor III

Re: [SCRIPTING] Make a new 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!

Re: [SCRIPTING] Make a new table

Not sure what you mean. Can you elaborate?

jumiprado
New Contributor III

Re: [SCRIPTING] Make a new table

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!

Re: [SCRIPTING] Make a new table

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;

Capture.PNG

jumiprado
New Contributor III

Re: [SCRIPTING] Make a new table

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

Can you help me with this?

Thanks You!

Re: [SCRIPTING] Make a new table

Made some changes. See if this helps

Community Browser