topic Re: Creating Groups of data in QlikView

Creating Groups of data

Wed, 14 Dec 2016 13:28:26 GMT

I have some data that looks like this:

"MID" is a component. "Duty Cycle", "Service Condition", and "Title" are characteristics that describe each "MID". Questions "A", "B", and "C" are asked for each "MID", and each question has an answer.

What I need to do is group all the "MID"s that have the same "Duty Cycle", "Service Condition", "Title", and "Answers" so that I can call upon those groups to see things like how many "MID"s are in each group. In the example above, I highlighted the "MID"s that would be grouped together in this example. 1 would group with 2, 3 would group with 5, and 4, 6, and 7 would be in their own groups.

I was thinking the best way to do this was write a code that would create a new column and assign numbers to each "MID", and wherever the code came across a set of "Answer"s, "Duty Cycle"s, and "Service Condition"s that have already been read, it would assign the same number to that "MID"... Can somebody show me how to do what I suggested, or show me a better way to get what I need?

Thanks.

Re: Creating Groups of data

Gysbert_Wassenaar — Wed, 14 Dec 2016 13:31:10 GMT

Something like this:

LOAD

MID,

Question,

Answer,

[Duty Cycle],

[Service Condition],

Title,

AutoNumber( MID & '|' & Answer & '|' & [Duty Cycle] & '|' & [Service Condition] & '|' & Title) as GroupID

FROM

...

;

Re: Creating Groups of data

Wed, 14 Dec 2016 13:49:09 GMT

I think this will help, but there are two things that might be a problem.

First, I need the groups to be dependent on the "Answer"s to the "Question"s also, not just "Duty Cycle", "Service Condition", and "Title".

Second, I am using a SQL load to pull this data from a database, and these fields are not all from the same table. "MID", "Duty Cycle", and "Service Condition" are from "Table 1". "Question" and "Answer" are from "Table 2". "Title" is from "Table 3".

Would it be best to concatenate the "Question" and "Answer" fields so I can include it in my autonumber function? If so, where should the concatenate function and the autonumber function go with respect to the SQL load?

Re: Creating Groups of data

vinieme12 — Wed, 14 Dec 2016 14:30:14 GMT

can you post sample data with each table in separate tabs! and a tab to show expected output!

It's easier to comprehend than to read and visualise.

Re: Creating Groups of data

Wed, 14 Dec 2016 14:39:14 GMT

The "New Identifier" column in the last sheet is how I want the data to be grouped.

Re: Creating Groups of data

Wed, 14 Dec 2016 14:48:18 GMT

I made some changes to my data when I created this post in hopes of making it simpler. I still need the "MID"s to be grouped with other "MID"s that have the same "Service Condition", "Duty Cycle", "Title", and "Answer"s to each "Question".

Re: Creating Groups of data

vinieme12 — Wed, 14 Dec 2016 15:14:55 GMT

Check the Attached

Map_TemplateID:

Mapping LOAD

[Template ID],

Title

FROM

(ooxml, embedded labels, table is tbtemplates);

TEMP:

LOAD [Template ID],

Applymap('Map_TemplateID',[Template ID]) as Title,

MSID,

MID

FROM

(ooxml, embedded labels, table is tbmstrategy);

Left JOIN(TEMP)

LOAD MID,

[Duty Cycle],

[Service Condition]

FROM

(ooxml, embedded labels, table is tbequipment);

Left JOIN(TEMP)

LOAD Question_ID,

MSID,

Answer

FROM

(ooxml, embedded labels, table is tbquestionanswers);

NoConcatenate

FACT:

LOAD * ,

AutoNumber([Duty Cycle] & '|' & [Service Condition] & '|' & Title) as GroupID

RESIDENT TEMP;

DROP TABLE TEMP;

Re: Creating Groups of data

Gysbert_Wassenaar — Wed, 14 Dec 2016 16:27:36 GMT

All the questions from different MIDS should have the same answers then? Perhaps like this then:

Data:

LOAD

MID,

Question,

Answer,

[Duty Cycle],

[Service Condition],

Title

FROM

...

;

JOIN ( Data)

LOAD

MID,

[Duty Cycle],

[Service Condition],

Title,

RESIDENT

Data

GROUP BY

MID,

[Duty Cycle],

[Service Condition],

Title

;

Re: Creating Groups of data

ganeshsvm — Tue, 27 Dec 2016 13:37:52 GMT

You might try like this,

After Joining all the tables of the sheets in one table:

TEMP1:
LOAD *,
if(MID=Peek(MID),Peek(ConcatKEy)&Key1,Key1) as ConcatKEy;
LOAD *,
Question_ID&[Service Condition]&[Duty Cycle]&Title&Answer as Key1,
Resident Final
Order by MID;

FINAL:

LOAD *, AutoNumber(NEWKEY) as NEWGRP;
LOAD *,
if(MID=Peek(MID),Peek(NEWKEY),ConcatKEy) as NEWKEY
Resident FINAL1
Order by MID asc, ConcatKEy desc;

DROP Table TEMP1,FINAL1;

And finally the field NEWGRP contains the group No.s

And also it was a good exercise.

Regards,

Ganesh