QlikView App Dev

[#1]

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.

[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

     ...

     ;

[#3]

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]

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.

[#5]

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

[#6]

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]

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;

[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

    ;

[ganeshsvm]

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