Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

How to Get the count for change in column value

hi,

i am looking for script which give me the count for chnage in column value.

My source table look like

Date                P_ID               Status

1 jan 2012          1                    Green

2 jan 2012          1                    Green

3 jan 2012          1                    RED

4 jan 2012          1                    Green

5 jan 2012          1                    Green

6 jan 2012          1                    RED

7 jan 2012          1                    RED

8 jan 2012          1                    RED

.........

1 Feb 2012          2                    Green

2 Feb 2012          2                    Green

3 Feb 2012          2                    RED

4 Feb 2012          2                    RED

5 Feb 2012          2                    Green

6 Feb 2012          2                    Green

........

and output which i need look lke

Month          P_ID          count of Change to Red

Jan 2012         1               2

Feb 2012         2              1

thanks

8 Replies
er_mohit
Honored Contributor II

Re: How to Get the count for change in column value

use this expression:

count(distinct P_ID)

for colour use

count(colour)

hope it helps you

Not applicable

Re: How to Get the count for change in column value


Hello,

I used theese data, similar to yours:

DateP_IDChange
jan 1Green
jan1Green
jan1RED
jan1Green
jan1Green
jan1RED
jan1RED
jan1RED
feb2Green
feb2Green
feb2RED
feb2RED
feb2Green

I load it in QV as is, and then created Straight table with Date & P_ID as Dimentions, and this simple expression with set modifier:

TextCount(Change)-count({<Change={Green}>} Change)

In first part I am counting all the lines in Change row, in second only the values that are green, so I minus them from whole range and here it is included as attachment. It is simple but not only the way to do something like that, everything depends on current tass within whole QVW model.

If you will use further more complex indicators and will add more colors you should change the logic of it.

If you have any questions, ask.

Not applicable

Re: How to Get the count for change in column value

Thanks for update

I am looking for how many time  product status change to red in each  month.

Not applicable

Re: How to Get the count for change in column value

It is exactly my solution do.

Not applicable

Re: How to Get the count for change in column value

You can also write srict set modifier without minusing:

The Expression

count({<Change={RED}>} Change) and it will count only red status, so you will see the same picture.

Not applicable

Re: How to Get the count for change in column value

thanks for your reply

Your sol give count of total no of red. My requirment is only count those red where  previous record is green.

mean  how many time prod status is change from green to red

in above data table for jan we shoud get 2 for prod id 1. as it is only change two time in month of jan from green to red.

thanks

Not applicable

Re: How to Get the count for change in column value

Yes, I understand it.

But as I see in your data, in Jan product ID 1 is changed to RED four times, am I correct?

Not applicable

Re: How to Get the count for change in column value

but i am looking for only those where it is change from green to red

Community Browser