Comparison between monthly loads

[liviumac]

Suppose I have the following MONTHLY data structure:

Month, Client_id, Monthly_Sales

I load this data every month, from different files - one file for each month

in Qlik I put all this information in a single table, where the field Month becomes just another field in the table and includes all the actual months I have data for

my question is this:

how can I write a script which identifies, each month, which clients are new compared to the previous month?

I need to create a dimension where I flag these new clients, each month.

so if now we are in August, i need a script which flags all clients which were not present in July in the August data, and so on

after 1 year, I need to be able to answer the question: "how much have we sold to clients which were new clients in that year?"

I cannot do that simply by comparing the month of December with January, because I might have clients which were new, but we only had them as clients for 1 month (say in August) and they are no longer in the December table - although they are still new clients from a yearly perspective

thank you

LM

[felipedl]

Hi Liv,

An example for january and february, that would flag Client_id 11,12 and 13 as new customers.

fakedataJan:

Load * Inline

[

Client_id,Month,Monthly_Sales

1,2017-01-01,100

2,2017-01-01,900

3,2017-01-01,500

4,2017-01-01,1300

5,2017-01-01,1400

6,2017-01-01,100

7,2017-01-01,99

8,2017-01-01,50

9,2017-01-01,10

10,2017-01-01,104

];

NoConcatenate

fakedataFeb:

Load * Inline

[

Client_id,Month,Monthly_Sales

1,2017-02-01,100

2,2017-02-01,900

3,2017-02-01,500

4,2017-02-01,1300

5,2017-02-01,1400

6,2017-02-01,100

9,2017-02-01,99

11,2017-02-01,50

12,2017-02-01,10

13,2017-02-01,104

];

data:

Load

Client_id as Customer,

date#(Month,'YYYY-MM-DD') as Date,

Monthly_Sales,

1 as [Flag Already in base]

Resident fakedataJan;

Concatenate(data)

Load

Client_id as Customer,

date#(Month,'YYYY-MM-DD') as Date,

Monthly_Sales,

0 as [Flag Already in base]

Resident fakedataFeb

where not exists(Customer,Client_id);

drop tables fakedataJan,fakedataFeb;

Felipe

[felipedl · Accepted solution]

Can't seem to edit my own post here,

so I'm just adding another one with a sample QVW.

Below the code, wich gives the client_id 11,12 and 13 as new customers.

fakedataJan:

Load * Inline

[

Client_id,Month,Monthly_Sales

1,2017-01-01,100

2,2017-01-01,900

3,2017-01-01,500

4,2017-01-01,1300

5,2017-01-01,1400

6,2017-01-01,100

7,2017-01-01,99

8,2017-01-01,50

9,2017-01-01,10

10,2017-01-01,104

];

NoConcatenate

fakedataFeb:

Load * Inline

[

Client_id,Month,Monthly_Sales

1,2017-02-01,100

2,2017-02-01,900

3,2017-02-01,500

4,2017-02-01,1300

5,2017-02-01,1400

6,2017-02-01,100

9,2017-02-01,99

11,2017-02-01,1

12,2017-02-01,2

13,2017-02-01,3

];

customers:

Load

distinct Client_id as Customer,

1 as [Flag Already in base]

Resident fakedataJan;

Concatenate(customers)

Load

distinct Client_id as Customer,

0 as [Flag Already in base]

Resident fakedataFeb

where not exists(Customer,Client_id);

NoConcatenate

data:

Load

*

Resident fakedataJan;

Concatenate(data)

Load

*

Resident fakedataFeb;

drop tables fakedataJan,fakedataFeb;

left join(data)

Load

Customer as Client_id,

[Flag Already in base]

Resident customers;

drop table customers;

Felipe.

[liviumac]

thank you for your reply

unfortunately, your script loses some information that was in the initial tables, i.e. the sales from February for clients existing in January

probably has to do with this part where not exists(Customer,Client_id)

for my application it is important to preserve the initial databases as they were but add this extra flag - so even if we calculated cummulated monthly sales, so that the total sales figure is correct, I still need to know how much we sold to each customer each month

(obviously my data structure does not have anything to do with the example in terms of fields, just the problem is the same in nature)

[liviumac]

yes this seems to do the trick

thank you, I will study the code, since it's a bit complicated for me

[felipedl]

Most of the operation is done by using the not exists clause to load the customers that aren't in present in the previous month,

Exists ‒ QlikView

The other statements are just table management with the load clause.

Glad it helped .