who are the duplicate customers who had quarterly index difference of 4 and less

[#1]

I am using qlikview personal edition and like to seek help for the question below. can someone assist me on the scripts?

I have a excel table (subset of this data is shown below) which I will be loading in to qlikview.

Customer	Quarterly Index
A	1
B	1
C	1
D	1
A	4
B	5
C	5
D	3
A	9
B	8
C	9
D	10

I would like to know:

1. Who are the same/duplicate customer with quarterly index of 3 and lesser

2. Who are the same/duplicate customer with quarterly index of 4 and more

answer should be somehow be the below which is able to highlight to me what the index difference in 1st cycle and 2nd cycle.

Customer	Quarterly Index	Cycle
A	3	1st cycle
B	4	1st cycle
C	4	1st cycle
D	2	1st cycle
A	5	2nd cycle
B	3	2nd cycle
C	4	2nd cycle
D	7	2nd cycle

[tyagishaila]

Try it,

aggr ( if (Quarterly Index<=3, '1stCycle' , '2ndCycle' ),Quarterly Index,Customer)

[#3]

using a table box or to put the script in backend (Load script portion?

[jagan]

Hi,

Try like this

Temp:

LOAD

Customer,

QuarterlyIndex

FROM Temp;

Data:

LOAD

*,

If(Previous(Customer) = Customer, QuarterlyIndex - Previous(QuarterlyIndex)) AS Diff;

LOAD

*

RESIDENT Temp

GROUP BY Customer, QuarterlyIndex;

DROP TABLE Temp;

Now in chart try like this:

Chart : Straight Table

Dimension: Customer, Diff

Expression: If(Diff <=3, '1st Cycle',  If(Diff > 3, '2nd Cycle')

Hope this helps you.

Regards,

Jagan.

[tyagishaila]

Check it,

if possible share qvw.

[tyagishaila]

Hi Jagan,

I tried it, but error generate

Data:

LOAD

*,

If(Previous(Customer) = Customer, QuarterlyIndex - Previous(QuarterlyIndex)) AS Diff;

LOAD

*

RESIDENT Temp

GROUP BY Customer, QuarterlyIndex;

Error: Aggregation expressions required by GROUP BY clause

Regards,

Shaila

[jagan]

Sorry, it is not Group By it is Order by.

Try like this

Temp:

LOAD

Customer,

QuarterlyIndex

FROM Temp;

Data:

LOAD

*,

If(Previous(Customer) = Customer, QuarterlyIndex - Previous(QuarterlyIndex)) AS Diff;

LOAD

*

RESIDENT Temp

ORDER BY Customer, QuarterlyIndex;

DROP TABLE Temp;

Now in chart try like this:

Chart : Straight Table

Dimension: Customer, Diff

Expression: If(Diff <=3, '1st Cycle',  If(Diff > 3, '2nd Cycle')

Hope this helps you.

Regards,

Jagan.

[#8]

the chart expression does not work. it show a blank chart.

[#9]

sorry it works.

[jagan]

Calculating in script is an easier one.

Regards,

Jagan.