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: 
Odelya
Contributor
Contributor

Find and summerize changes in data set

Hi

This is my data

 

ContractStatusAMOUNTSTATUS_DATE_FromSTATUS_DATE_NextChange Number
100170010-287110.5609/08/2017 00:0020/08/2017 00:001
1001700572320.7520/08/2017 00:0020/08/2017 00:002
10017005072320.7520/08/2017 00:0030/08/2017 00:003
10017006072320.7530/08/2017 00:0030/08/2017 00:004
10017009072320.7530/08/2017 00:0024/09/2017 00:005
10017009672320.7524/09/2017 00:0024/12/2017 00:006
10017001072320.7524/12/2017 00:0020/08/2017 00:007
10017003069285.1820/08/2017 00:0020/08/2017 00:008
10017005069285.1820/08/2017 00:0030/08/2017 00:009
10017006069285.1830/08/2017 00:0030/08/2017 00:0010
10017009069285.1830/08/2017 00:0024/09/2017 00:0011
10017009669285.1824/09/2017 00:0024/12/2017 00:0012
10017009869285.1824/12/2017 00:0024/12/2017 00:0013
100170010069285.1824/12/2017 00:0024/12/2017 00:0014
10017009069285.1824/12/2017 00:0024/12/2017 00:0015
100170010769285.1824/12/2017 00:0024/12/2017 00:0016
100170011069285.1824/12/2017 00:0024/12/2017 00:0017
100170012069285.1824/12/2017 00:0024/12/2017 00:0018
100170013069285.1824/12/2017 00:0025/12/2017 00:0019
100170013469285.1825/12/2017 00:0025/12/2017 00:0020
100170013669285.1825/12/2017 00:0025/12/2017 00:0021
1001700137800000.0025/12/2017 00:0025/12/2017 00:0022
1001700139800000.0025/12/2017 00:0026/12/2017 00:0023
1001700140800000.0026/12/2017 00:0026/12/2017 00:0024
100170014169285.1826/12/2017 00:0004/01/2018 00:0025
100170014269285.1804/01/2018 00:0014/01/2018 00:0026
100170016069285.1814/01/2018 00:0001/01/2999 00:0027

 

I need to present the amount of time there was a change in the contract information:

  1. When there is a change for a lower status (change line number 2,7,15)
  2. When there is an amount of change (lower or higher) (change line number 2,8,22,25)

I also need to summarize the number of times for each change listed above…

Any suggestions?

 

Thank you!

1 Solution

Accepted Solutions
sergio0592
Specialist III
Specialist III

Hi,

In the preceding load of your load statement try to add:

LOAD *,
if(Status<Previous(Status),1,0) as FL_change,
if(rowno()>1 and AMOUNT<>Previous(AMOUNT),1,0) as FL_Amount

View solution in original post

2 Replies
sergio0592
Specialist III
Specialist III

Hi,

In the preceding load of your load statement try to add:

LOAD *,
if(Status<Previous(Status),1,0) as FL_change,
if(rowno()>1 and AMOUNT<>Previous(AMOUNT),1,0) as FL_Amount
Odelya
Contributor
Contributor
Author

Thank you!