Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Sirs ,
For below data i wanted output like
customer QUALITY for year 2017 and 2018 , with minimum priority and Logistics as OV , HYBRID as null and class as A .
NEEDED RESULT
CUST | 2017 - MIN PRI QUALITY | 2018 - MIN PRI QUALITY |
CUSTOMER 1 | 8 | 5 |
CUSTOMER 2 | 8 | 3 |
CUSTOMER 3 | - | 8 |
INPUT TABLE :
CUSTOMER | YEAR | PRIORITY | logistics | HYBRID | CLASS | QUALITY_ON_TEN |
CUSTOMER 1 | 2018 | 6 | 9 | |||
CUSTOMER 1 | 2017 | 3 | HY | A | 1 | |
CUSTOMER 1 | 2018 | 4 | NU | LY | 5 | |
CUSTOMER 1 | 2017 | 4 | NU | LY | A | 2 |
CUSTOMER 1 | 2017 | 2 | OV | 7 | ||
CUSTOMER 1 | 2017 | 1 | OV | A | 8 | |
CUSTOMER 1 | 2018 | 2 | OV | A | 5 | |
CUSTOMER 1 | 2018 | 3 | OV | HY | A | 7 |
CUSTOMER 2 | 2017 | 4 | HY | 6 | ||
CUSTOMER 2 | 2017 | 6 | NU | LY | A | 1 |
CUSTOMER 2 | 2018 | 7 | NU | LY | A | 6 |
CUSTOMER 2 | 2018 | 1 | OV | 3 | ||
CUSTOMER 2 | 2017 | 3 | OV | 8 | ||
CUSTOMER 2 | 2017 | 0 | OV | A | 8 | |
CUSTOMER 2 | 2018 | 1 | OV | A | 3 | |
CUSTOMER 2 | 2018 | 3 | OV | HY | 6 | |
CUSTOMER 2 | 2018 | 5 | OV | HY | A | 6 |
CUSTOMER 2 | 2017 | 2 | OV | LY | A | 1 |
CUSTOMER 3 | 2018 | 5 | A | 4 | ||
CUSTOMER 3 | 2018 | 6 | NU | 5 | ||
CUSTOMER 3 | 2018 | 2 | NU | A | 7 | |
CUSTOMER 3 | 2018 | -3 | OV | A | 8 | |
CUSTOMER 3 | 2017 | 2 | OV | A | 8 | |
CUSTOMER 3 | 2018 | 6 | OV | A | 0 | |
CUSTOMER 3 | 2017 | 1 | A | 8 |
My expression which did not work
Concat(DISTINCT{<YEAR={2017}>*<logistics={'OV'}>*<HYBRID_L={0}>*<PRIORITY={"$(=FirstSortedValue(PRIORITY,aggr(min(PRIORITY),CUSTOMER,PRIORITY)))"}>}QUALITY_ON_TEN)
Concat(DISTINCT{<YEAR={2018}>*<logistics={'OV'}>*<HYBRID_L={0}>*<PRIORITY={"$(=FirstSortedValue(PRIORITY,aggr(min(PRIORITY),CUSTOMER,PRIORITY)))"}>}QUALITY_ON_TEN)
@anushree1 , @Anil_Babu_Samineni
Sir you all gave me solution for one previous doubt . hence adding you
Hi Please find the attached , output on qv matches your desired output except for Customer 3 I am unsure if its typo error on customer 3 or the logic different, please check and let me know
Thank You Anushree1 for the reply .
Actually for customer 3 for 2017 the lowest priority is 1 , but its logistics field is NULL , though CLASS is A ...
Hence , it should have Null result
while for 2018 the lowest priority is - 3 hence it should display 8 as result
our search cond should like be
1) first find the lowest priority
2) check if corresponding Logistics is OV , if yes then
3) Check class if its A , if yes then populate corresponding QUALITY_ON_TEN
else populating Null
Hope i am explaining myself clear .
I made little change in data and your expression in not giving expected result ;
Final:
Load *,
if(len(trim(HYBRID))=0,1,0) as Null_Flag;
Temp:
LOAD * INLINE [
CUSTOMER, YEAR, PRIORITY, logistics, HYBRID, CLASS, QUALITY_ON_TEN
CUSTOMER 1, 2018, 6, , , , 9
CUSTOMER 1, 2017, 3, , HY, A, 1
CUSTOMER 1, 2018, 4, NU, LY, , 5
CUSTOMER 1, 2017, 4, NU, LY, A, 2
CUSTOMER 1, 2017, 2, OV, , , 7
CUSTOMER 1, 2017, 8, OV, , A, 8
CUSTOMER 1, 2017, 1, OV, , A, 10
CUSTOMER 1, 2018, 7, OV, , A, 5
CUSTOMER 1, 2018, 1, OV, , A, 9
CUSTOMER 1, 2018, 3, OV, HY, A, 7
CUSTOMER 2, 2017, 4, , HY, , 6
CUSTOMER 2, 2017, 6, NU, LY, A, 1
CUSTOMER 2, 2018, 7, NU, LY, A, 6
CUSTOMER 2, 2018, 1, OV, , , 3
CUSTOMER 2, 2017, 3, OV, , , 8
CUSTOMER 2, 2017, 0, OV, , A, 8
CUSTOMER 2, 2018, 1, OV, , A, 3
CUSTOMER 2, 2018, 3, OV, HY, , 6
CUSTOMER 2, 2018, 5, OV, HY, A, 6
CUSTOMER 2, 2017, 2, OV, LY, A, 1
CUSTOMER 3, 2018, 5, , , A, 4
CUSTOMER 3, 2018, 6, NU, , , 5
CUSTOMER 3, 2018, 2, NU, , A, 7
CUSTOMER 3, 2018, -3, OV, , A, 8
CUSTOMER 3, 2017, 2, OV, , A, 8
CUSTOMER 3, 2018, 6, OV, , A, 0
CUSTOMER 3, 2017, 1, , , A, 8
];
10 should be the result for 2017 , 9 for 2018 .