Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Need to get a straight table data based on one attribute

Hi,

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

Notice NoPart CodePrimary AuthorSecondary AuthorStart DateEnd DateDescriptionRemarkStatus
N1001200Josh01
N1001300Josh01
N1002400Ben03
N1002400Ben03
N1002200Ben03
N1003500Josh01
N1004600Ken21
N1004700Ken21
N1007600Sam31
N1009600Sam31

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 NoPart CodePrimary AuthorSecondary AuthorStart DateEnd DateDescriptionRemarkStatus
N1001200Josh01
N1002400Ben03
N1003500Josh01
N1004700Ken21
N1007600Sam31
N1009600Sam31

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

Abhijit

8 Replies
vishsaggi
Champion III
Champion III

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

Hi,

Abhijit,

Please give some more details on this

Thanks

Not applicable
Author

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

Not applicable
Author

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

vishsaggi
Champion III
Champion III

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
Partner - Champion III
Partner - Champion III

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

Not applicable
Author

Hi Peter,

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

Abhijit