Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

hopkinsc
Valued Contributor II

finding the latest date where sales exist for all customers (in script)

Hi all,

i need to update a variable in the script that holds the latest date where sales exist for all customers.

basically, i load in data for all of the customers, some customers are quicker at supplying data than others, i only want to report on the latest date where i have received data for everyone. i need to get this done in the script then once i have found the latest date i need to store the date in a variable. i then want to use the variable later in the script to create some flags against dates.

I am looking for help to get the script written to find the latest date first.

can anyone help please?

i have attached a sample and included a sample excel file with 2 customers in it.

the excel file has data for Cust A that goes from 04/01/2016-27/11/2016 and data for Cust B from 04/01/2016- 09/10/2017.

so the resulting variable should state 09/10/2017 as this is the last date that data has been received for both customers.

can anyone help please?

Thanks in advance

1 Solution

Accepted Solutions

Re: finding the latest date where sales exist for all customers (in script)

Try this

Calendar:

LOAD cal_CALENDAR_YYYYMMDD as Date,

    cal_CALENDAR_YEAR,

    cal_CALENDAR_WEEK_NO,

    cal_FISCAL_PERIOD,

    cal_FISCAL_YEAR,

    cal_FISCAL_PERIOD_NO,

    cal_FISCAL_WEEK_NO,

    cal_CALENDAR_MONTH_TEXT,

    cal_COUNTER

FROM

Sample.xlsx

(ooxml, embedded labels, table is Calendar);


Data:

LOAD Customer,

    Date,

    Volume

FROM

Sample.xlsx

(ooxml, embedded labels, table is [Cust A]);


Concatenate(Data)

LOAD Customer,

    Date,

    Volume

FROM

Sample.xlsx

(ooxml, embedded labels, table is [Cust B]);


TempTable:

LOAD Max(Date) as MaxDate

Where Count = 2;

LOAD Date,

Count(DISTINCT Customer) as Count

Resident Data

Where Len(Trim(Volume)) > 0

Group By Date;


LET vMaxDate = Peek('MaxDate');

DROP Table TempTable;

11 Replies

Re: finding the latest date where sales exist for all customers (in script)

Try this

Calendar:

LOAD cal_CALENDAR_YYYYMMDD as Date,

    cal_CALENDAR_YEAR,

    cal_CALENDAR_WEEK_NO,

    cal_FISCAL_PERIOD,

    cal_FISCAL_YEAR,

    cal_FISCAL_PERIOD_NO,

    cal_FISCAL_WEEK_NO,

    cal_CALENDAR_MONTH_TEXT,

    cal_COUNTER

FROM

Sample.xlsx

(ooxml, embedded labels, table is Calendar);


Data:

LOAD Customer,

    Date,

    Volume

FROM

Sample.xlsx

(ooxml, embedded labels, table is [Cust A]);


Concatenate(Data)

LOAD Customer,

    Date,

    Volume

FROM

Sample.xlsx

(ooxml, embedded labels, table is [Cust B]);


TempTable:

LOAD Max(Date) as MaxDate

Where Count = 2;

LOAD Date,

Count(DISTINCT Customer) as Count

Resident Data

Where Len(Trim(Volume)) > 0

Group By Date;


LET vMaxDate = Peek('MaxDate');

DROP Table TempTable;

t_chetirbok
Valued Contributor

Re: finding the latest date where sales exist for all customers (in script)

I attached the file.

please, check

t_chetirbok
Valued Contributor

Re: finding the latest date where sales exist for all customers (in script)

this script will work only in case when we always have the exact number of customers (2 for now)

Re: finding the latest date where sales exist for all customers (in script)

Yes, and I am guessing that OP will already know what is the max number of customer.... this can be hard-coded or calculated in the script... but again based on the assumption that we know the max number of customer, we can very easily use a variable in the where clause

t_chetirbok
Valued Contributor

Re: finding the latest date where sales exist for all customers (in script)

yeah, agree.

But, you know, I don't believe my customers and always do all automated calculations.

There were so many times of "I forgot", "I didn't think", "I didn't tell you about changes", etc. from customers side.

Also I think, it's good to have an universal solution)

Re: finding the latest date where sales exist for all customers (in script)

Well then if that is the case and this number can go up and down, then neither of our calculations are probably going to work. The basis of both of our scripts resides on the fact that number of customers are not going to change very often, because if it does, then how do you really know which number is correct... for example

May 2017 10

June 2017 8

July 2017 10

August 2017 8

September 2017 7

Now, which one would you say is the correct Month? It could be August because the number of customer went down by 2 or it could be September because customer went down another one.

My point is, that if we can hard-code something to simplify our script.... we should do it... because Group Bys are resource intensify and I would want to avoid it as much as possible

hopkinsc
Valued Contributor II

Re: finding the latest date where sales exist for all customers (in script)

Hi Both,

Thanks for your suggestions, i have tried using Sunny's suggestion and am,ended it slightly to work out the no.of customers instead of hard coding.

but i cant get it working, i have attached a new sample with some more realistic data (the real data also has a branch assigned to the customer, so there are multiple entries for each date).

i can't see what is wrong, could you please help out if you have time?

many thanks

t_chetirbok
Valued Contributor

Re: finding the latest date where sales exist for all customers (in script)

as I can see, we don't have any dates where all 3 customers have value

t_chetirbok
Valued Contributor

Re: finding the latest date where sales exist for all customers (in script)

I've just simplify script a little bit:

Data:

load distinct

sales_date,

count(distinct customer) as Customer_Count

FROM Data.qvd (qvd)

//where len(sales_volume)>0

Group by sales_date;

Customer:

outer join (Data)

load count(Distinct customer) as Customer_Count_total

FROM Data.qvd (qvd);

Variable:

load

max(sales_date) as Max_Date

Resident Data

where Customer_Count_total=Customer_Count;

LET vDate = date(peek('Max_Date'));

drop Tables Data, Variable;

Community Browser