Need to get a straight table data based on one attribute

[#1]

Hi,

I have a master data (fact table) as follows which I will be showing in one of the report:

Notice No	Part Code	Primary Author	Secondary Author	Start Date	End Date	Description	Remark	Status
N1001	200	Josh01
N1001	300	Josh01
N1002	400	Ben03
N1002	400	Ben03
N1002	200	Ben03
N1003	500	Josh01
N1004	600	Ken21
N1004	700	Ken21
N1007	600	Sam31
N1009	600	Sam31

From this table I need to show another straight table which will consist of distinct "Notice No" and "Part No" which is most consistent with the field "Notice No". e.g. Notice No N1002 should show me Part Code as 400 as it is most consistent. The output should be like this:

Notice No	Part Code	Primary Author	Secondary Author	Start Date	End Date	Description	Remark	Status
N1001	200	Josh01
N1002	400	Ben03
N1003	500	Josh01
N1004	700	Ken21
N1007	600	Sam31
N1009	600	Sam31

How to achieve this Data Model in QlikView? What would be the optimize way for this?

Abhijit

[vishsaggi]

What you mean by most consistent here ? Can you elaborate please?

[sunny_talwar]

What all your dimensions and what all your expressions here? May be if you can offer some details? May be a sample can also help?

[soloeeeoff]

Hi,

Abhijit,

Please give some more details on this

Thanks

[#5]

Here is my loading script (Data Source also has the same Columns):

[JSR Data]:

LOAD

     NOTICE,

     PREFIX,

     BASE,

     SUFFIX,

     [PART NAME],

     [NOTICE TYPE],

     [MODEL YEAR],

     [LEAD VEHICLE],

     [WERS STATUS],

     Date([CAD IN] , 'MM/DD/YYYY') AS [CAD IN],

     Date([ENG SO], 'MM/DD/YYYY') AS [ENG SO],

     PRIMARY,

     DESIGNER,

     ENGINEER,

     PARTS,

     CPSC,

     REMARKS,

     [ACTIVITY REMARKS],

     [CAUSAL FACTOR]

FROM

[JSR_2016-05-26.xlsx]

(ooxml, embedded labels, table is Sheet1);

• There are multiple notices with same name.
• I need to show 2 different tables
• Table 1 would consist of all the above attributes(Columns) as it is. So its quite straight forward. Just fetching the data in straight table from source.
• Table 2 would consist of following columns

     NOTICE,
     [NOTICE TYPE],
     [MODEL YEAR],      [LEAD VEHICLE],      [WERS STATUS],      Date([CAD IN] , 'MM/DD/YYYY') AS [CAD IN],      Date([ENG SO], 'MM/DD/YYYY') AS [ENG SO],      PRIMARY,      DESIGNER,      ENGINEER,      PARTS,      CPSC,      REMARKS,      [ACTIVITY REMARKS],      [CAUSAL FACTOR]

• Table 2 should consist of unique NOTICE number and data associated with it. Here is the catch. There are multiple CPSC codes associated with a single NOTICE. I need to show a CPSC code which is most consistent with that particular NOTICE. i.e. if a particular CPSC code associated with that NOTICE has maximum count it should show. Else it can show any CPSC.

How to achieve this?

Abhijit

[#6]

Hi Sunny.. Please check my reply to latest responder.

[vishsaggi]

Can you share your sample example excel file to work on if possible ?

You can load the 2nd table using NoConcatenate with DISTINCT Notice. Like

FirstLoad:

LOAD ...,

         ...

FROM

[JSR_2016-05-26.xlsx]

(ooxml, embedded labels, table is Sheet1);

NoConcatenate

2ndLoad:

LOAD DISTINCT NOTICE,

         ...,

         YourFields

FROM

[JSR_2016-05-26.xlsx]

(ooxml, embedded labels, table is Sheet1);

[Peter_Cammaert]

Some further deciphering: "Maximum count" meaning that the record in Table 2 should contain the values for the highest PART CODE. Since you don't show us any actual data, what should happen when this "Maximum Count" occurs more than once (with different values in other fields)?

[#9]

Hi Peter,

If the maximum count occurs more than once, then it should pick any of that maximum count PART CODE.

Abhijit