How to Count Values in Sequences from a Database Column

[aftabn10]

Hi, I am looking for some help in regards to counting sequences that I have in a column that sits in my database that I have linked my Qlik Dashboard to. I am currently looking to find out the different times a customer visits a store and the values in the database are stored in a sequence like the following:

AM@AM@PM@AM@PM

the maximum sequence is 7 as this is the maximum a customer has visited. Now what I am looking to do is create a pie chart from this to combine the individual counts from the sequence of every row in order to then derive an accurate pie chart that will tell me when customers visit a store.

For example the above sequence would give me the following count:

AM: 3
PM: 2

So if I created a pie chart from the figures above I would have 60% for AM and 40% for PM but I need to do this across 60 different sequence values and I am not sure how I do this in Qlik. 

Would really appreciate any advice on how I could resolve this issue.

[kaushiknsolanki]

Hi,

You can create a separate row for each of this sequence using Subfield() function in Qlik Sense.

Once you get that you can then easily create the pie chart with that.

Your script should look like below.

Load *, Subfield(Sequence,'@') as AM/PM from xyz

Now you can create a Pie chart with xyz as dimension and count(xyz) as an expression.

[aftabn10]

Thank you so much Kaushiknsolanki, I will give that a go and let you know how I get on.

[aftabn10]

HI Kaushiknsolanki, I tried running the script as you mentioned but I get the following error:

The following error occurred:

No qualified path for file: ***

The error occurred here:

Load *, Subfield(time,'@') as AM_PM from time_seq

Any ideas?

[kaushiknsolanki]

Hi,

You need to give proper path of your Table, in your case it is time_seq.

Once you give proper path it should work.