Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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.

Tags (1)
8 Replies
Gysbert_Wassenaar
Not applicable

Re: Creating Groups of data

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

Re: Creating Groups of data

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
Not applicable

Re: Creating Groups of data

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.

Not applicable

Re: Creating Groups of data

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

Not applicable

Re: Creating Groups of data

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
Not applicable

Re: Creating Groups of data

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;

Gysbert_Wassenaar
Not applicable

Re: Creating Groups of data

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
Not applicable

Re: Creating Groups of data

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