topic Re: Need to get a straight table data based on one attribute in QlikView

Need to get a straight table data based on one attribute

Fri, 23 Sep 2016 18:20:52 GMT

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

Re: Need to get a straight table data based on one attribute

vishsaggi — Fri, 23 Sep 2016 21:36:35 GMT

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

Re: Need to get a straight table data based on one attribute

sunny_talwar — Fri, 23 Sep 2016 22:26:05 GMT

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?

Re: Need to get a straight table data based on one attribute

soloeeeoff — Mon, 26 Sep 2016 07:02:44 GMT

Hi,

Abhijit,

Please give some more details on this

Thanks

Re: Need to get a straight table data based on one attribute

Mon, 26 Sep 2016 20:22:16 GMT

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

Re: Need to get a straight table data based on one attribute

Tue, 27 Sep 2016 16:51:24 GMT

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

Re: Need to get a straight table data based on one attribute

vishsaggi — Tue, 27 Sep 2016 18:27:13 GMT

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);

Re: Need to get a straight table data based on one attribute

Peter_Cammaert — Tue, 27 Sep 2016 19:14:21 GMT

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)?

Re: Need to get a straight table data based on one attribute

Tue, 27 Sep 2016 19:45:16 GMT

Hi Peter,

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

Abhijit