Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi at all,
I have a situation in which i have to check a dataset like the following one:
Cod_Customer | Cod_Doc |
---|---|
A600 | DDT |
A600 | FAE |
A600 | FAXN |
B150 | FAXN |
B150 | FAX |
C300 | DDT |
C302 | DDT |
D400 | FAX |
D402 | FAX |
E900 | FAE |
I would like to create a script code in order to generate a new table with only the Cod_Customer record that don't have a DDT or FAE as Cod_Doc.
For example in this case the new table should be like this one
Cod_Customer |
---|
B150 |
D400 |
D402 |
Any idea about how to solve it?
Thanks in advance
Andrea
Source:
LOAD
*
FROM
[https://community.qlik.com/thread/285960]
(html, codepage is 1252, embedded labels, table is @1)
;
left join (Source)
LOAD
Cod_Customer,
1 as FlagExclude
RESIDENT Source
WHERE
match(Cod_Doc, 'DDT', 'FAE');
Final:
LOAD
Distinct Cod_Customer
Resident Source
Where NOT Alt(FlagExclude, 0);
Source:
LOAD
*
FROM
[https://community.qlik.com/thread/285960]
(html, codepage is 1252, embedded labels, table is @1)
;
left join (Source)
LOAD
Cod_Customer,
1 as FlagExclude
RESIDENT Source
WHERE
match(Cod_Doc, 'DDT', 'FAE');
Final:
LOAD
Distinct Cod_Customer
Resident Source
Where NOT Alt(FlagExclude, 0);
table1:
load * inline [
Cod_Customer,Cod_Doc
A600,DDT
A600,FAE
A600,FAXN
B150,FAXN
B150,FAX
C300,DDT
C302,DDT
D400,FAX
D402,FAX
E900,FAE
];
table2:
load distinct if(sum(match(trim(Cod_Doc),'DDT','FAE'))=0,Cod_Customer) as Cod_Customer
resident table1
group by Cod_Customer
;
May be try this?
Cod:
LOAD Cod_Customer,
Cod_Doc
FROM
[https://community.qlik.com/thread/285960]
(html, codepage is 1252, embedded labels, table is @1);
INNER JOIN (Cod)
LOAD Cod_Doc
Resident Cod
WHERE NOT Match(Cod_Doc, 'DDT', 'FAE');