Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Creating Groups of data

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.

8 Replies
Gysbert_Wassenaar

Something like this:

LOAD

     MID,

     Question,

     Answer,

     [Duty Cycle],

     [Service Condition],

     Title,

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

FROM

     ...

     ;


talk is cheap, supply exceeds demand
Not applicable
Author

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?

vinieme12
Champion III
Champion III

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.

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Not applicable
Author

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

Not applicable
Author

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".

vinieme12
Champion III
Champion III

Check the Attached

243343.JPG

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;

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Gysbert_Wassenaar

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,

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

RESIDENT

    Data

GROUP BY

    MID,

    [Duty Cycle],

    [Service Condition],

    Title

    ;

   


talk is cheap, supply exceeds demand
ganeshsvm
Creator II
Creator II

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