Year/month dimensions

[Anonymous]

Hi all

This may get quite convoluted pretty quickly, so bear with me!

Ok, so I'm using Qlik Sense Server.

I have a data connection to quite a large table of data, which is basically client agreements dating back to 2003, and these agreements are for a variety of different products.

The table contains a huge amount of data (over 70,000 agreement details), but the columns I'm specifically talking about in this situation are 'EFF_START_DT', which is the date the agreement started, and 'EFF_END_DT', which, for agreements that have expired/been cancelled etc., the date the agreement ended.

I am building an app which I want to show, by month/year, the number of agreements that ended that month (either showing a full year by month, or select filter on selected months), and the base of agreements still active as of that month.

Now, I have managed to get it working for the Ended subs view, which was relatively simple, as I just made the date dimensions (month and year) based on the EFF_END_DT, (month(EFF_END_DT)), and counting the numbers of subs that ended that year/month, (count(AGREE_NUM)).

However, the issue I have now is I can't for the life of me work out how to incorporate the total number of subs still active as of that same month/year i.e. the EFF_END_DT is null.

Even if I have a completely different App for the base numbers of subs, how do I script something that beings back the number of active subs as of (for example) February 2017, or a full year view of 2016 showing the base growth month by month?

I really don't want to have a separate app though, which adds a whole lot of complexity.

Here is an example of the background script in the data load editor (it's greatly abridged - I'm selecting dozens of columns and have a lot of case statements to group products etc)

Select

AGREE_ID
,AGREE_NUM
,EFF_START_DT
,EFF_END_DT
,AGREE_STATUS

from
CX_LKP_RECURRING_AGREEMENTS_BASE

where EFF_START_DT <= getdate()
and year(EFF_START_DT) >= 2003

Thanks!

[Anonymous]

I do, and it used to work, but I can't get it to work now. I have repaired, and even uninstalled/reinstalled it, but it just starts up with a blank white window and does nothing else.

[sunny_talwar]

May be download and install the most recent version of Qlik Sense and then try

[Anonymous]

Thank you Sunny - that worked.

Really sorry to be a pain, but a couple of things.

As I said earlier, I can't add any columns to the table data (I have no control over the source tables, they are what they are) - so I can't add an 'EndCheck' column. How does that change the script?

And the format/syntax of the script is quite different than what I'm used to - I use SELECT instead of LOAD, for example, and I'm not sure what headings such as 'Calendar" do?

Does it matter, in the end though?

[sunny_talwar]

1) Not asking you to modify the source data to add another column. This would be done in the QlikView script using the existing fields.

Table:

LOAD AGREE_ID,

    AGREE_NUM,

    AGREE_STATUS,

    EFF_START_DT,

    EFF_END_DT,

    Date(If(EFF_END_DT = 'NULL', Today(), EFF_END_DT)) as [End Date],

    If(EFF_END_DT = 'NULL', MonthStart(Today(), 1), MonthStart(EFF_END_DT)) as EndCheck,

    BU_ID,

    AGREEMENT_PRODUCT_GROUP

FROM

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

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

If you see above, I am using EFF_END_DT to create a new field EndCheck just like you would do in SQL Select statement

2) LOAD is select equivalent of SELECT. Calendar is the name of the table, you can give this any name you would feel comfortable with.

[Anonymous]

Ah, I see, I got it backwards. Thank you, still learning the SQL ropes!

So, here's my script as it is now:

LIB CONNECT TO 'SQL OLAP';
[Agreement Summary]:
SQL

LOAD
    AGREE_ID,
    AGREE_NUM,
    AGREE_STATUS,
    EFF_START_DT,
    EFF_END_DT,
    Date(If(EFF_END_DT = 'NULL', Today(), EFF_END_DT)) as [End Date],
    If(EFF_END_DT = 'NULL', MonthStart(Today(), 1), MonthStart(EFF_END_DT)) as EndCheck,
    BU_ID,
    AGREEMENT_PRODUCT_GROUP

FROM
CX_LKP_RECURRING_AGREEMENTS_BASE

Calendar:
LOAD Date(MinDate + IterNo() - 1) as Date,
  Date(MonthStart(MinDate + IterNo() - 1), 'MMM-YYYY') as MonthName
While MinDate + IterNo() - 1 <= Today();
LOAD Min(EFF_START_DT) as MinDate
Resident Table;

IntervalMatch:
IntervalMatch(Date)
LOAD EFF_START_DT,
     [End Date]
Resident Table;

But I'm getting an 'Incorrect syntax near the keyword 'If'' error...

[sunny_talwar]

What is the exact error you are getting? Can you share a screenshot?

[Anonymous]

[sunny_talwar]

You might want to do like this

LIB CONNECT TO 'SQL OLAP';
[Agreement Summary]:

LOAD
    AGREE_ID,
    AGREE_NUM,
    AGREE_STATUS,
    EFF_START_DT,
    EFF_END_DT,
    Date(If(EFF_END_DT = 'NULL', Today(), EFF_END_DT)) as [End Date],
    If(EFF_END_DT = 'NULL', MonthStart(Today(), 1), MonthStart(EFF_END_DT)) as EndCheck,
    BU_ID,
    AGREEMENT_PRODUCT_GROUP;

SQL SELECT

     AGREE_ID,

     AGREE_NUM,

     AGREE_STATUS,

     EFF_START_DT,

     EFF_END_DT,

     BU_ID,

     AGREEMENT_PRODUCT_GROUP

FROM CX_LKP_RECURRING_AGREEMENTS_BASE;

This way of loading data is called Preceding Load where you pull data from SQL and on top do a QlikView load. You don't really need a qlikview load unless you want to do some manipulation.

[Anonymous]

Sigh.

[sunny_talwar]

You named your table to be Agreement Summary, so resident load will be from Agreement Summary and not Table

Calendar:

LOAD Date(MinDate + IterNo() - 1) as Date,

  Date(MonthStart(MinDate + IterNo() - 1), 'MMM-YYYY') as MonthName

While MinDate + IterNo() - 1 <= Today();

LOAD Min(EFF_START_DT) as MinDate

Resident [Agreement Summary];

IntervalMatch:
IntervalMatch(Date)
LOAD EFF_START_DT,
    [End Date]
Resident [Agreement Summary];