topic Re: Flagging a change in dimension value in QlikView

Flagging a change in dimension value

mattphillip — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Flagging a change in dimension value

pascos88 — Mon, 09 Jul 2018 11:10:28 GMT

Hi,

nor sure want do you want have in Changes column?

What do you want check ?

what er your condition ?

Best

Re: Flagging a change in dimension value

mattphillip — Mon, 09 Jul 2018 11:15:43 GMT

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!

Re: Flagging a change in dimension value

pascos88 — Mon, 09 Jul 2018 11:18:13 GMT

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/InterRecordFunctions/above.htm

Best,

Re: Flagging a change in dimension value

sunny_talwar — Mon, 09 Jul 2018 11:22:53 GMT

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;

Re: Flagging a change in dimension value

Anil_Babu_Samineni — Mon, 09 Jul 2018 11:26:04 GMT

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

Re: Flagging a change in dimension value

mattphillip — Mon, 09 Jul 2018 11:28:16 GMT

Hi Anil,

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

Re: Flagging a change in dimension value

mattphillip — Mon, 09 Jul 2018 13:22:36 GMT

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

Re: Flagging a change in dimension value

sunny_talwar — Mon, 09 Jul 2018 13:27:03 GMT

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);

Re: Flagging a change in dimension value

mattphillip — Tue, 10 Jul 2018 13:50:31 GMT

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

Re: Flagging a change in dimension value

sunny_talwar — Tue, 10 Jul 2018 13:52:04 GMT

It would be difficulty to know without seeing... but may be you need to fix your Order By Statement for the other dimensional fields you have in you actual data set?

Re: Flagging a change in dimension value

mattphillip — Tue, 10 Jul 2018 13:52:56 GMT

Thanks Sunny. I'll try that.