Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

kunkumnaveen
Contributor III

which join do i need to apply

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

(ooxml, embedded labels, table is Sheet1);

LEFT join

LOAD CLIENT,

   // LIFNR,

     VENDOR_NAME as name1,

     ROLE,

     DATE_OF_REVIEW,

     QUES_NO,

     ANSWER

FROM

(qvd);

LEFT join

LOAD CLIENT,

     QUES_NO,

     QUES_DESC

FROM

(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

(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

11 Replies
kunkumnaveen
Contributor III

Re: which join do i need to apply

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

kunkumnaveen
Contributor III

Re: which join do i need to apply

the views number is keep increasing but no one is suggesting how to resolve this ,plz i need help desperately

this is my last requirement of my project

MindaugasBacius
Valued Contributor II

Re: which join do i need to apply

Instead of this statement:

Right JOIN

LOAD QUES_NO,

    QUALITY

FROM

(ooxml, embedded labels, table is Sheet1);



Before the script write:


map_quality:

mapping LOAD QUES_NO,

    QUALITY

FROM

(ooxml, embedded labels, table is Sheet1);



And in one of your joins try using this:


LEFT join

LOAD CLIENT,

    QUES_NO,

    QUES_DESC,

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

FROM

(qvd);

kunkumnaveen
Contributor III

Re: which join do i need to apply

HI,thanks for your response man.,

looks like i got some thing but not a perfect one ..... ,

Untitled.png

after loading i tried to display it 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

kunkumnaveen
Contributor III

Re: which join do i need to apply

just try to click on screen short the table  will be more clear for you

kunkumnaveen
Contributor III

Re: which join do i need to apply

just try to click on screen short the table  will be more clear for you

deepaktibhe
Valued Contributor

Re: which join do i need to apply

Instead of right join use right keep so that u will get separate table if there are same column name rename it and use it in chart.

kunkumnaveen
Contributor III

Re: which join do i need to apply

right join or right keep that wont make diff ,i applying either of them  u will lose ur left side data

deepaktibhe
Valued Contributor

Re: which join do i need to apply

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.

Community Browser