topic Re: Filtering Data With Various Parameters in QlikView

Filtering Data With Various Parameters

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

Hi All

I have got data with various multiple parameters which are termed to be Norm or Expected which needs to be Analyzed.

Attached is a sample of data and I need to count the number of Citizens using the below Parameters.

Question	Norm
D.O.B	01/01/1990 - 31/12/2000
Family Size	Numeric
Employment Status	All Except Retired
Education Level	High School (or) Primary (of) Both
ID	Alphanumeric
TransactionType	Credit Card (&) Cash

Your help and advice is greatly appreciated.

Re: Filtering Data With Various Parameters

ngrunoz — Tue, 16 May 2017 08:24:33 GMT

Here with the attachment.

Re: Filtering Data With Various Parameters

prieper — Tue, 16 May 2017 09:17:59 GMT

Hi,

You may load the data and then try to construct a mega-heavy query on the field. But you will not have any flexibility.

Thus would try to normalize the data. Understand that CitizenCode, income, dob, education level, status and family size belongs to the citizen. Another database with 1:n would be Retailer and transaction? What sense would be then ID? seems to belong to Citizen as well?

Re: Filtering Data With Various Parameters

Anil_Babu_Samineni — Tue, 16 May 2017 09:26:19 GMT

Can you try this for one analysis

Count({<Question = {'D.O.B','Family Size','Employment Status','Education Level For Dependencies','ID','TransactionType'}>}CitizenCode)

Re: Filtering Data With Various Parameters

prieper — Tue, 16 May 2017 09:43:33 GMT

Further the above you may use a script like

RawData:

LOAD CitizenCode,

Question,

Response

FROM

[Citizen Info With Parameters.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Citizen: LOAD * INLINE [CitizenCode]; // Dummy to allow a JOIN

FOR EACH sQuestion IN 'Ave Income', 'D.O.B', 'Education Level For Dependencies', 'Employment Status', 'Family Size'

[$(sQuestion)]:

LOAD

CitizenCode,

Response AS [$(sQuestion)]

RESIDENT

RawData

WHERE

Question = '$(sQuestion)';

JOIN (Citizen) LOAD * RESIDENT [$(sQuestion)];

DROP TABLE [$(sQuestion)];

NEXT sQuestion

Transactions: LOAD * INLINE [CitizenCode]; // Dummy to allow a JOIN

FOR EACH sQuestion IN 'Retailer', 'TransactionDate', 'TransactionType'

[$(sQuestion)]:

LOAD

CitizenCode,

Response AS [$(sQuestion)]

RESIDENT

RawData

WHERE

Question = '$(sQuestion)';

JOIN (Transactions) LOAD * RESIDENT [$(sQuestion)];

DROP TABLE [$(sQuestion)];

NEXT sQuestion

DROP TABLE RawData;

and then do your usual filtering on the listboxes ....

Peter

Re: Filtering Data With Various Parameters

ngrunoz — Tue, 16 May 2017 13:37:15 GMT

What I would like to do is to count the number of Citizens who have D.O.B Between 2 date, Check if the ID (Identification Number) is in the correct format as its suppose to be Alphanumeric and length of 6 digits. Check if the Education Level falls within those prescribed criteria (Norm). etc.

Re: Filtering Data With Various Parameters

ngrunoz — Tue, 16 May 2017 13:41:40 GMT

I am failing to follow. If ok please may you take me step by step. As I am still new to qlikview.

Re: Filtering Data With Various Parameters

prieper — Tue, 16 May 2017 13:56:32 GMT

pfa

Peter

Re: Filtering Data With Various Parameters

ngrunoz — Tue, 16 May 2017 15:06:33 GMT

In Other words. It has to create a table with Values .

e.g the table from the attached data will Look like

This will have been grouped by the prescribed Parameters in Norm.

Question	Number Of Citizen
D.O.B	1
Family Size	3
Employment Status	2
Education Level For Dependencies	1
ID	3
TransactionType	1

Re: Filtering Data With Various Parameters

Anil_Babu_Samineni — Tue, 16 May 2017 16:00:50 GMT

Could be your data is wrong?

In fact, I have check the data and there is no such information for Response. Even we can apply filter for that. Without data it won't effect

Re: Filtering Data With Various Parameters

ngrunoz — Wed, 17 May 2017 07:16:27 GMT

My apologies maybe I expressed my requirements wrongly.

I have that data set in CSV and then I want that data to create a summary table counting number of Citrizens who fall in the criteria of the parameters below

Question	Norm
D.O.B	01/01/1990 - 31/12/2000
Family Size	Numeric
Employment Status	All Except Retired
Education Level	High School (or) Primary (of) Both
ID	Alphanumeric
TransactionType	Credit Card (&) Cash

That for example if the question is D.O.B then it will count the number of citizen who have the response which fall in between 01/01/1990 & 31/12/2000; if the question is Employment Status then it counts all the Citizens with employment status which is not Retired ......... etc.

Re: Filtering Data With Various Parameters

Anil_Babu_Samineni — Wed, 24 May 2017 02:38:11 GMT

Like this? May be helps

Re: Filtering Data With Various Parameters

vishsaggi — Wed, 24 May 2017 02:49:14 GMT

Seems to be a duplicate thread. Check here:

Conditional Flagging

Re: Filtering Data With Various Parameters

ngrunoz — Wed, 24 May 2017 14:26:15 GMT

Is there a way of doing it in script in the event that they are a lot of conditions.

Re: Filtering Data With Various Parameters

Anil_Babu_Samineni — Wed, 24 May 2017 16:47:20 GMT

But if we use The same condition in Script, May be you won't get all Question values

Re: Filtering Data With Various Parameters

ngrunoz — Thu, 25 May 2017 12:12:10 GMT

thanks