Filtering Data With Various Parameters

[ngrunoz]

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.

[ngrunoz]

Here with the attachment.

[prieper]

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?

[Anil_Babu_Samineni]

Can you try this for one analysis

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

[prieper]

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

[ngrunoz]

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.

[ngrunoz]

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

[prieper]

pfa

Peter

[ngrunoz]

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

[Anil_Babu_Samineni]

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