Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to add conditionally calculated count of values

Hi all,

There is an issue I'm fighting with in Qlik Sense but still don't get a solution, maybe you could help, community.

I have a table consisted of user_ids and around 30 other columns. The thing is that I want to assign an ascending count for unique users when they appear again and again.

Let me give an example (of  what I have):

User_ID

Transaction_Id
User_1231
012484129412

safjorw29

3
nick_19834
User_123

5

927417461246
safjorw297

And this is what I want to get:

User_ID

Transaction_IdUsers_Lifetime_Transaction_Count
User_12311
0124841294121

safjorw29

31
nick_198341
User_123

5

2
9274174612461
safjorw2972

As you see, 2 users appear twice in the example and the required field returns 2 for them, that should also work for any number of purchases by the same customer.

I hope I managed to explain my question.

Please help, I would really appreciate any ideas.

Thank you in advance!

1 Solution

Accepted Solutions
shubham_singh
Partner - Creator II
Partner - Creator II

Try this in script side

UserTransaction:

LOAD * FROM UserTransaction.qvd;

Noconcatenate

UserTransaction2:

LOAD * Resident UserTransaction

Order By User_ID;


Noconcatenate

UserTransaction3:

LOAD

*,

if(User_ID=peek(User_ID),peek(UserLifetimeTransactionCount)+1,1) as UserLifetimeTransactionCount

Resident UserTransaction2;


Drop Table UserTransaction,UserTransaction2;

View solution in original post

7 Replies
volakakis
Contributor II
Contributor II

Try sum(aggr(sum(DISTINCT USER_ID))  or smth like this (I am a newbie)

shubham_singh
Partner - Creator II
Partner - Creator II

Try this in script side

UserTransaction:

LOAD * FROM UserTransaction.qvd;

Noconcatenate

UserTransaction2:

LOAD * Resident UserTransaction

Order By User_ID;


Noconcatenate

UserTransaction3:

LOAD

*,

if(User_ID=peek(User_ID),peek(UserLifetimeTransactionCount)+1,1) as UserLifetimeTransactionCount

Resident UserTransaction2;


Drop Table UserTransaction,UserTransaction2;

shubham_singh
Partner - Creator II
Partner - Creator II

Although transaction ID should be sequential, use Order By User_ID,Transaction_Id just to be safe.

Not applicable
Author

Thank you for your reply!

But this approach results in duplicating rows. For example if a user ID occurs 123 times in a table it would return 123 rows with the same transaction ID, money ets. Consequently, this affects other calculations

shubham_singh
Partner - Creator II
Partner - Creator II

My answer will give you exactly what you asked for. I am only adding a column to an existing table, if your table contains multiple rows of same transaction ID it will still be the same. Give some sample data and be specific about your problem.

Not applicable
Author

Solved a thing with duplications...

But now i face synthetic keys.

Снимок.PNG

As we load all data from the orginal database and create a new one with same fields it comes up with a syn.key based on transactions and user_id's. Do you have any idea on how to solve it?

And the whole concept about duplications was when i drop transactions field ar alias it to avoid a key i get this kind of a table (why is it so?). On the table below a single user is selected (he has 17 transactions in total)

Снимок2.PNG

Many thanks to you! That's a great help you did for me!

shubham_singh
Partner - Creator II
Partner - Creator II

You don't need to create another table of user transactions.

I wrote 3 load statements, 1st one is to just load the data from a file, 2nd one is to do order by because qlik won't let me do that in 1st one and 3rd one is to add a column of LifetimeUserTransaction.

You can skip first load statement if table is already in you model.

I wrote LOAD star in all of them to keep all the fields, then you have to drop table in 1st and 2nd load.

The number of tables before and after this code shoud be same, just a field in added to trasaction table.

You can use "Rename table UserTransaction3 to Transactions".

-Shubham Singh