Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
Try it,
aggr ( if (Quarterly Index<=3, '1stCycle' , '2ndCycle' ),Quarterly Index,Customer)
using a table box or to put the script in backend (Load script portion?
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.
Check it,
if possible share qvw.
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
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.
the chart expression does not work. it show a blank chart.
sorry it works.
Calculating in script is an easier one.
Regards,
Jagan.