Flagging a change in dimension value

[mattphillip]

Hoping someone can help with this. I've included a small dummy table to illustrate what I'm trying to accomplish. The actual table has many thousands of rows and a wider date range. Essentially, I've been asked to enhance our current weekly report to flag where an applicant's offer status (OfferStatus field) has changed. I've tried both the peek function in the load script and the above function as part of an expression along with the aggr function in a chart but with limited success. Neither method is allowing me to compare the offer stats by applicant, rather its simply comparing them in a linear fashion as the rows appear in the table.

Does anyone have any ideas as to whether this is possible? (comparing the offer status by applicant to produce a expression value)

Any help would be most appreciated.

Thanks,

Matt

Applicant ID	Date	Programme	Stage	OfferStatus	Changed? (Y/N)
AP01	12/10/2017	Law	1	Conditional
AP02	05/09/2017	Accounting	2	Decline
AP01	20/10/2017	Law	1	Conditional-Insurance
AP03	07/10/2017	Art & Design	3	Unconditional
AP02	10/09/2017	Accounting	1	Conditional
AP03	16/10/2017	Art & Design	3	No Response
AP02	02/10/2017	Accounting	1	Conditional-Firm
AP04	19/10/2017	Counselling	1	Unconditional
AP05	18/10/2017	Artchitecture	2	Decline

[pascos88]

Hi,

nor sure want do you want have in Changes column?

What do you want check ?

what er your condition ?

Best

[mattphillip]

Ideally, I'd like to add a flag saying either Yes (if the offer status for that applicant has changed) or No if it hasn't.

Thanks!

[pascos88]

maybe you need something like :

if(OfferStatus = 'Changed', 1, 0)  if you can have the Changed string in this field.

if you need to compare the OfferStatus with the previous one , maybe you need to use the function Above().

https://help.qlik.com/en-US/sense/June2018/Subsystems/Hub/Content/ChartFunctions/InterRecordFunction...

Best,

[sunny_talwar]

Did you try using Peek() in combination of the Order By Statement... unless you use order by on your Applicant ID, you won't be comparing within the same Applicant. Order by can be used only in the resident load... so try something like this

Table:

LOAD * INLINE [

    Applicant ID, Date, Programme, Stage, OfferStatus

    AP01, 12/10/2017, Law, 1, Conditional

    AP02, 05/09/2017, Accounting, 2, Decline

    AP01, 20/10/2017, Law, 1, Conditional-Insurance

    AP03, 07/10/2017, Art & Design, 3, Unconditional

    AP02, 10/09/2017, Accounting, 1, Conditional

    AP03, 16/10/2017, Art & Design, 3, No Response

    AP02, 02/10/2017, Accounting, 1, Conditional-Firm

    AP04, 19/10/2017, Counselling, 1, Unconditional

    AP05, 18/10/2017, Artchitecture, 2, Decline

];

FinalTable:

LOAD *,

If([Applicant ID] = Previous([Applicant ID]), If(OfferStatus = Previous(OfferStatus), 'No', 'Yes'), 'No') as [Changed? (Y/N)]

Resident Table

Order By [Applicant ID], Date;

DROP Table Table;

[Anil_Babu_Samineni]

Is this what you are expecting?

Applicant ID Date           Programme      Stage      OfferStatus                Changed? (Y/N)

AP01           12/10/2017      Law                1             Conditional                          No

AP02           05/09/2017      Accounting   2            Decline                                    No

AP01           20/10/2017      Law                1            Conditional-Insurance           Yes

AP03           07/10/2017      Art & Design 3             Unconditional                     No

AP02           10/09/2017      Accounting   1                Conditional                  Yes

AP03           16/10/2017      Art & Design 3                No Response                Yes

AP02           02/10/2017      Accounting   1                Conditional-Firm           Yes

AP04           19/10/2017      Counselling  1                Unconditional                No

AP05           18/10/2017      Artchitecture 2                Decline                          No

[mattphillip]

Hi Anil,

Yes that's what I'm looking for as an outcome.

M

[mattphillip]

Hi Sunny,

I tried the above code, loading the dummy data in from the xlsx file but I keep getting an error saying the field name must be unique. I can't see any problem though.

Table:

LOAD [Applicant ID],

     Date,

     Programme,

     Stage,

     OfferStatus,

     [Changed? (Y/N)]

FROM

[\\vmwfil01\homedrives01$\m513730\change flag test 09-07-18.xlsx]

(ooxml, embedded labels, table is Sheet1);

FinalTable:

LOAD *,

If([Applicant ID] = Previous([Applicant ID]), If(OfferStatus = Previous(OfferStatus), 'No', 'Yes'), 'No') as [Changed? (Y/N)]

Resident Table

Order By [Applicant ID], Date;

DROP Table Table;

Any ideas?

Thanks,

Matt

[sunny_talwar]

If you are creating a field called [Changed? (Y/N)]... why do you have it in Excel or loading from Excel? Try without the field from Excel

LOAD [Applicant ID],

     Date,

     Programme,

     Stage,

     OfferStatus,

     [Changed? (Y/N)]

FROM

[\\vmwfil01\homedrives01$\m513730\change flag test 09-07-18.xlsx]

(ooxml, embedded labels, table is Sheet1);

[mattphillip]

Thanks Sunny,

It works with the dummy data set now. However, when I connected it to the actual data (the only differences being a higher number of rows, more additional columns and loading multiple .csv files from a folder), it works for around 85% of cases. For some reason it seems to randomly or mistakenly mark identical entries in the offerstatus field as being changed.

Don't suppose you have any idea why this might be happening? I've had a look but can't see an obvious or obscure reason.

Any help would be most appreciated.

Matt