topic Re: Filtering a Pivot Table in QlikView

Filtering a Pivot Table

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

Goodday,

I'm trying to work out two challenges I would like to work out on a Pivot Table.

My current output looks something along the lines of the below.

Order ID	Error	Category	Date and Time
1234567	TS12:Therecipient'sZIPcodeisincorrect,pleasecheck.	Postal Code	10-05-2019 13:21:27
1234568	TS12:Therecipient'sZIPcodeisincorrect,pleasecheck.	Postal Code	29-04-2019 11:53:22
1234571	120124:Therequestedserviceisunavailablebetweentheselectedlocations	Service level	07-05-2019 08:24:22
1234571	120124:Therequestedserviceisunavailablebetweentheselectedlocations	Service level	07-05-2019 08:41:40
1234573	[TS28:Nozonefound:PleasecheckthatthereisnocountrycodeinfrontoftheZIPcodeandthattheservicelevelisincludedinyourcarriercontractforthedestination.]	Postal Code	01-05-2019 13:51:38
1234592	121210:Theselectedserviceisnotavailablefromtheorigintothedestination.	Service level	06-05-2019 12:24:44
1234593	128400:InvalidCountryCodeRE	Country	06-05-2019 11:17:58
1234593	121210:Theselectedserviceisnotavailablefromtheorigintothedestination.	Service level	03-05-2019 13:06:58
1234594	120217:ShipTophonenumbercannotbemorethan15digitslong	Phone Number	06-05-2019 10:59:32

I would like to only display the Order and Error the first time it occurs. This means that for 1234571, I would only like it to be displayed once, for 07-05-2019 08:24:22. For 1234593 I would only loke to see the 121210 error with it's timestamp.

I can have the script generate a separate table filtering this all out, but as I've got other tables and graphs that require both instances to have the data, I would like this to be in the display side, for efficiency purposes.

My second question would be regarding whether I would be able to have a 'set' selector on this Pivot Table. I've currenlty got a causer set by "Company" / "Customer", and in this pivot table, I would only like to see causer = Customer instances.
I have got other selectors as well, where those are supposed to influence this Pivot Table, as well as my other tables, so I do not think I can use an Alternate Status for this while having both this Pivot and other to be influenced by the different selectors. How would I go about this?

Re: Filtering a Pivot Table

dplr-rn — Wed, 15 May 2019 19:43:01 GMT

Use timestamp as an expression and give Min(timestampfield) . remove the other 2 columns
not clear on your second question

Re: Filtering a Pivot Table

Naule — Thu, 16 May 2019 06:44:18 GMT

Simply stating to remove two coloumns where I inquire about a result displaying four columns is not helpful at all.
Expressing the timestamp field as a min does not solve the question for the example of 1234593.

Let me rephrase the question:

In my data model, I have built a tabel along the lines of below:

Order ID	Error	Category	Date and Time	Causer
1234567	Error TS12	Postal Code	10-5-2019 13:21	Customer
1234568	Error TS12	Postal Code	29-4-2019 11:53	Customer
1234569	Error 123467	Connection	10-5-2019 13:21	Company
1234570	Error 123467	Connection	7-5-2019 8:41	Company
1234571	Error 120124	Service level	7-5-2019 8:24	Customer
1234571	Error 120124	Service level	7-5-2019 8:41	Customer
1234572	Error 123466	Connection	7-5-2019 8:24	Company
1234573	Error TS28	Postal Code	1-5-2019 13:51	Customer
1234592	Error 121210	Service level	6-5-2019 12:24	Customer
1234593	Error 128400	Country	6-5-2019 11:17	Customer
1234593	Error 121210	Service level	3-5-2019 13:06	Customer
1234594	Error 120217	Phone Number	6-5-2019 10:59	Customer

In my table, I only want to show errors based on being the first error that occurs for the order and it has to be caused by the customer.

The table as above is used for other types of output as well and I would prefer not to have to built a new table in the data model to retrieve the output.
The output has to be responsive to 3 of the 4 selectors going on in the report (all but "causer") so I do not think I can use an Alternate State on the table to achieve part 2.

Re: Filtering a Pivot Table

dplr-rn — Thu, 16 May 2019 11:19:39 GMT

Do you realize there's a reason I asked to remove 2 columns.

Have no interest in helping anymore