Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Number of Occurances

Hi All, I have a dataset of the customers to an F&B outlet at a hotel.

I would like to show the number of repeat guests to the restaurant each month so need to count the occurrences of each guest name and then show only those greater than 1 by month.  Can anyone advise on the script for this?

In the bar chart I would just like to show each month the number of repeat guests which I will show as a percentage of total guests for the month.


Thanks for your help!

1 Solution

Accepted Solutions
manucamon
Valued Contributor III

Re: Number of Occurances

Hi Rusell,

With your data and this script, this will be your output:

Data:

LOAD Date,

     MonthName(Date) as MonthName,

     RowNo()         as RowNum,

     Time,

     BillNo,

     Total,

     [Guest Name]

FROM

[Data for Occurance Question.xlsx]

(ooxml, embedded labels, table is Sheet1);

Occurences:

LEFT JOIN (Data)

LOAD

    MonthName,

    COUNT(DISTINCT RowNum) as Occurrence,

    [Guest Name]

RESIDENT Data

GROUP BY MonthName, [Guest Name];

FinalData:

LOAD

    MonthName,

    COUNT(DISTINCT RowNum) as Times,

    COUNT(DISTINCT [Guest Name]) as Customers

RESIDENT Data

WHERE Occurrence > 1

GROUP BY MonthName;

Regards!!

7 Replies

Re: Number of Occurances

Hi Russell,

Can you please share the sample data along with the expected output.

Regards,

Kaushik Solanki

Not applicable

Re: Number of Occurances

Hi Kaushik, thank you for your reply. Some data is attached here.

I would like to eliminate all guests with an occurrence of only 1 and to

sum (or count) the remaining.

e.g. if Paul has 3 occurrences, Russell and 2 Occurrences and Steve has 1

occurrence.

By 1st eliminating Steve who has only 1 occurrence the sum would be 5 and

the count would be 2.

Thanks for your help, really appreciated.

Russell

On Tue, Aug 30, 2016 at 4:59 PM, Kaushik Solanki <qcwebmaster@qlikview.com>

manucamon
Valued Contributor III

Re: Number of Occurances

Hi Rusell,

With your data and this script, this will be your output:

Data:

LOAD Date,

     MonthName(Date) as MonthName,

     RowNo()         as RowNum,

     Time,

     BillNo,

     Total,

     [Guest Name]

FROM

[Data for Occurance Question.xlsx]

(ooxml, embedded labels, table is Sheet1);

Occurences:

LEFT JOIN (Data)

LOAD

    MonthName,

    COUNT(DISTINCT RowNum) as Occurrence,

    [Guest Name]

RESIDENT Data

GROUP BY MonthName, [Guest Name];

FinalData:

LOAD

    MonthName,

    COUNT(DISTINCT RowNum) as Times,

    COUNT(DISTINCT [Guest Name]) as Customers

RESIDENT Data

WHERE Occurrence > 1

GROUP BY MonthName;

Regards!!

Not applicable

Re: Number of Occurances

Thank you so much, that's perfect!

manucamon
Valued Contributor III

Re: Number of Occurances

You're wellcome

anushree1
Valued Contributor

Re: Number of Occurances

Could you please let me know how you could get the customers as 10 , i have got everything else right but customers i am not able to get the right number,can you tell me what expression you used in the chart

manucamon
Valued Contributor III

Re: Number of Occurances

Hi, I use a count distinct: COUNT(DISTINCT [Guest Name])


Regards!!