11 Replies Latest reply: Jul 5, 2016 4:18 AM by naveen kumar RSS

    which join do i need to apply

    naveen kumar

      SuppilerScore:

      LOAD

      CLIENT,

            VENDOR_NAME,

           ROLE,

           DATE_OF_REVIEW,

          QM_SCORE,

           CS_SCORE,

          GENERAL_SCORE,

           PROC_SCORE,

           MANU_SCORE,

         round(TOTAL_SCORE) as TOTAL_SCORE ,

         pick(match(month(DATE_OF_REVIEW),'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'),

      'Q4','Q4','Q4','Q1','Q1','Q1','Q2','Q2','Q2','Q3','Q3','Q3') as QUATERS,

      'FY'& Date(AddYears(yearstart (DATE_OF_REVIEW, 1, 4),-1),'YY')  &'-'& Date(yearstart (DATE_OF_REVIEW, 1, 4),'YY') as YEAR

      FROM

      [..\QVDS\SuppilerScore.qvd]

      (qvd);


      CrossTable(QUALITY,SCORE,8)

      LOAD

      CLIENT,

      VENDOR_NAME as name1,

           ROLE,

           DATE_OF_REVIEW,

           TOTAL_SCORE,

           QUATERS,

           YEAR,

          YEAR&QUATERS AS YEARQUATER ,

            QM_SCORE AS [Quality Management],

             CS_SCORE as [Customer Service],

           GENERAL_SCORE as General,

          PROC_SCORE as Procurement ,

         MANU_SCORE as Manufacture 

        Resident SuppilerScore;

          DROP Table SuppilerScore;

         

      LEFT join

       

      [Suppiler Rating]:

      LOAD TOTAL_SCORE,

             SUPCAT

      FROM

      [C:\qlikview\QVDS\Supplier Categorization.xlsx]

      (ooxml, embedded labels, table is Sheet1);

       

       

      LEFT join

      LOAD CLIENT,

         // LIFNR,

           VENDOR_NAME as name1,

           ROLE,

           DATE_OF_REVIEW,

           QUES_NO,

           ANSWER

      FROM

      [C:\qlikview\QVDS\SupplierQuestionNumber.qvd]

      (qvd);

       

      LEFT join

      LOAD CLIENT,

           QUES_NO,

           QUES_DESC

      FROM

      [C:\qlikview\QVDS\SupplierQuestions.qvd]

      (qvd);

       

      till this my data flow looks ok

      Untitled.png

      so now i am trying to group particular QUES_NO under each QUALITY VALUE

      i mean

      suppose for    Customer Service(value)

         

      QUES_NOQUALITY
      Q1_CSCustomer Service
      Q1_DELIVCustomer Service
      Q1_TECHCustomer Service
      Q2_CSCustomer Service
      Q2_DELIVCustomer Service
      Q2_TECHCustomer Service
      Q3_CSCustomer Service
      Q3_DELIVCustomer Service
      Q3_TECHCustomer Service
      Q4_DELIV

      Customer Service

       

      i have done all the grouping in excel sheet and loaded in to application

      Right JOIN

      LOAD QUES_NO,

          QUALITY

      FROM

      [C:\qlikview\QVDS\Supplier_Ques_Map.xlsx]

      (ooxml, embedded labels, table is Sheet1);

       

      after loading I applied RIGHT JOIN b/w this table and rest of the table,then i got wat i am looking

      right.png

      but unfortunately my right side entire data is gone ........

      then i applied left join,i got my data back but grouping thing is not working

      left.png

      so anyone suggestion me how to make both the thing to work,i mean data and group

       

      PFA grouping excel sheet

        • Re: which join do i need to apply
          naveen kumar

          sorry for miss spelling

          but unfortunately my right side entire data is gone ........

          when i applied RIGHT JOIN  my left side entire data is gone

          • Re: which join do i need to apply
            Deepak Tibhe

            What is your requirement exactly.

            Why u cant create new table using resident data.And apply right join if u find duplicate columns use qualify , unqualify.

              • Re: which join do i need to apply
                naveen kumar

                Hi,

                as suggested by mindaugasbacius i have used apply map function to group QUES_NO  with Quality

                map_quality:

                mapping LOAD QUES_NO,

                    QUALITY

                FROM

                [C:\qlikview\QVDS\Supplier_Ques_Map.xlsx]

                (ooxml, embedded labels, table is Sheet1);

                 

                SuppilerScore:

                LOAD

                CLIENT,

                     //LIFNR,

                     VENDOR_NAME,

                     ROLE,

                     DATE_OF_REVIEW,

                   

                     QM_SCORE,

                     //QM_MAX,

                     CS_SCORE,

                    // CS_MAX,

                     GENERAL_SCORE,

                     //GENERAL_MAX,

                     PROC_SCORE,

                    // PROC_MAX,

                     MANU_SCORE,

                    // MANU_MAX,

                     round(TOTAL_SCORE) as TOTAL_SCORE ,

                     //MAX_SCORE

                      //'Q'&Ceil(MONTH(DATE_OF_REVIEW)/3) AS QUATERS,

                   // year(DATE_OF_REVIEW) as YEAR

                        pick(match(month(DATE_OF_REVIEW),'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'),

                'Q4','Q4','Q4','Q1','Q1','Q1','Q2','Q2','Q2','Q3','Q3','Q3') as QUATERS,

                'FY'& Date(AddYears(yearstart (DATE_OF_REVIEW, 1, 4),-1),'YY')  &'-'& Date(yearstart (DATE_OF_REVIEW, 1, 4),'YY') as YEAR

                // Year(yearstart (DATE_OF_REVIEW, 1, 4)) as YEAR

                FROM

                [..\QVDS\SuppilerScore.qvd]

                (qvd);

                 

                 

                CrossTable(QUALITY,SCORE,8)

                LOAD

                CLIENT,

                VENDOR_NAME as name1,

                     ROLE,

                     DATE_OF_REVIEW,

                     TOTAL_SCORE,

                     QUATERS,

                     YEAR,

                    YEAR&QUATERS AS YEARQUATER ,

                      QM_SCORE AS [Quality Management],

                       CS_SCORE as [Customer Service],

                     GENERAL_SCORE as General,

                    PROC_SCORE as Procurement ,

                   MANU_SCORE as Manufacture 

                  Resident SuppilerScore;

                    DROP Table SuppilerScore;

                   

                  LEFT join

                 

                [Suppiler Rating]:

                LOAD TOTAL_SCORE,

                       SUPCAT

                FROM

                [C:\qlikview\QVDS\Supplier Categorization.xlsx]

                (ooxml, embedded labels, table is Sheet1);

                 

                LEFT join

                LOAD CLIENT,

                   // LIFNR,

                     VENDOR_NAME as name1,

                     ROLE,

                     DATE_OF_REVIEW,

                     QUES_NO,

                     ANSWER

                FROM

                [C:\qlikview\QVDS\SupplierQuestionNumber.qvd]

                (qvd);

                LEFT join

                LOAD CLIENT,

                    QUES_NO,

                    QUES_DESC,

                    Applymap('map_quality', QUES_NO, ':') as QUALITY

                FROM

                [C:\qlikview\QVDS\SupplierQuestions.qvd]

                (qvd);

                 

                 

                 

                After doing that my outcome is this ,which looks ok

                 

                 

                apply.png

                now i want to display them in a straight table

                Dimeansion:QUES_DESC

                Expression:=FirstSortedValue({<ROLE={EVAL}>}ANSWER,-DATE_OF_REVIEW)

                by  ENABLING suppress null value for QUES_DESC  dimenasion

                i got this (column with zero value are missing)

                Untitled.png

                 

                so if you think ,the first slide table data flow is a proper flow,then can you plz suggest me wat expression do i need to writing down so that even the column with ZERO answer should also display

                 

              • Re: which join do i need to apply
                naveen kumar

                Any more suggestions will be appreciated........................