Calculating field based in a field of another table in Load script

[erezzoni]

Hi I want to categorize a set of clients based on the spending amount.

The idea is to calculate the quartiles of the spent sum of all clients and then put each client ia a category.

For example:

I calculate first each quartile in a table :

Segments:
Load
fractile(Money,0.25) as Quartile_25,
fractile(Money,0.50) as Quartile_50,
fractile(Money,0.75) as Quartile_75

from Clientes

	Money
Quartile 25% (Q25)	$100.00
Quartile 50% (Q50)	$300.00
Quartile 75% (Q75)	$600.00

And then i need, for each client, to calculate which segment he is in based in the money field calculated in the table i created before (Segments):

Segment
1	Spent < Q25
2	Spent > Q25 and Spent < Q50
3	Spent > Q50 and Spent < Q75
4	Spent > Q75

How can I do this? Must I use variables? how can i compare a certain field with a field of another table?

Thanks in advance

[blaise]

Intervalmatch() 

[erezzoni]

Thanks for your answer, and sorry to ask: how can I use intervalmatch in this case?

[blaise · Accepted solution]

Two tables;

Intervals:

Segment, lower_value,higher_value

Spend:

Client,amount

Left join (spend)

Intervalmatch (amount)

Load

  Lower_value,

  Higher_value

Resident

Intervals;

Left join (client)

Load * resident intervals;

I'm on mobile now so Its hard to format the text but I hope you get the point. Help.qlik.com has some good examples on how intervalmatch works

[erezzoni]

Thanks again BLaise!

I understand now how it is supposed to work.

Still, i feel a little silly but, i need to ask: how can I dynamically load the first table, the Intervals one?

I mean, I only have a sales table, where i have the name of the client and tha amount spent.

I need to dynamically calculate the first, second and third quartile and assign a name for each segment.

[erezzoni]

Thanks again BLaise!

I understand now how it is supposed to work.

Still, i feel a little silly but, i need to ask this: how can I dynamically load the first table, the Intervals one?

I mean, I only have a sales table, where i have the name of the client and tha amount spent.

I need to dynamically calculate the first, second and third quartile and assign a name for each segment.