Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello
We have a table that was loaded from an excel file
TAB1:
Zona | Field1 | Field2 | Field3 | Num |
A | 12 | 3 | usr1 | 1 |
B | 4 | 6 | usr2 | 1 |
C | 2 | 7 | usr3 | 1 |
D | 7 | 7 | usr2 | 1 |
E | 1 | 1 | usr1 | 1 |
F | 4 | 5 | usr5 | 1 |
G | 6 | 7 | usr2 | 1 |
we need exclude from the table TAB1 the records that have a value in Field3 that was in the table TAB2
TAB2:
Field3 |
usr1 |
the TAB2 also is loaded from an excel file.
any body know how i can exclude the records in Tab1 where the field3 = usr1 ???
thanks in advance for your help
regards
try something like this...
Tab2:
LOAD Field3 AS FIELD3
FROM
Qlik_123921.xlsx
(ooxml, embedded labels, table is Tab2);
TAB1:
LOAD Zona,
Field1,
Field2,
Field3,
Num
FROM
Qlik_123921.xlsx
(ooxml, embedded labels, table is Tab1)
where not Exists(FIELD3,Field3);
Drop table Tab2;
Regards,
Pradeep
Hi,
first load table TAB2, then load table TAB1 where not exists(Field3)..
- Ralf
hi first load tab2 then join/keep of tab2 for tab 1
Hello
i have loaded the tble TAb2 first and add where not exists(Field3), but smething is wrong the system supress all records of usr1 (right) but also supress several records with the value usr2 and usr3, now
Field1 | Field2 | Field3 | Zona |
2 | 7 | usr3 | C |
4 | 5 | usr5 | F |
4 | 6 | usr2 | B |
it seem that there is only one record by value Field3.
!
hello
how i can do this "First load tab2 then join/keep of tab2 for tab 1"?? i try
Tab1:
inner keep (Tab2) LOAD Zona,
Field1, ...
but don't work...
thanks
regards
Ok, I see. You will need to use another function: lookup
Just post your script and I can adjust..
try something like this...
Tab2:
LOAD Field3 AS FIELD3
FROM
Qlik_123921.xlsx
(ooxml, embedded labels, table is Tab2);
TAB1:
LOAD Zona,
Field1,
Field2,
Field3,
Num
FROM
Qlik_123921.xlsx
(ooxml, embedded labels, table is Tab1)
where not Exists(FIELD3,Field3);
Drop table Tab2;
Regards,
Pradeep
thanks, it works with ...where not Exists(FIELD3,Field3);..
regards
Hi,
Please try like this,
QUALIFY Field3;
TAB1:
LOAD * INLINE [
Zonal, Field1, Field2, Field3, Num
A, 12, 3, usr1, 1
B, 4, 6, usr2, 1
C, 2, 7, usr3, 1
D, 7, 7, usr2, 1
E, 1, 1, usr1, 1
F, 4, 5, usr5, 1
G, 6, 7, usr2, 1
];
QUALIFY Field3;
TAB2:
LOAD * INLINE [
Field1, Field2, Field3, Zonal
2, 7, usr3, C
4, 5, usr5, F
4, 6, usr2, B
6, 6, usr6, D
];
TAB3:
LOAD Field1 as F1, Field2 as F2,TAB2.Field3 as F3, Zonal as Z1 Resident TAB2
Where not Exists(TAB1.Field3,TAB2.Field3);
Regards,
Raja.
Hello
Could you explain how to use the lookup funciones, to do the same, the scrip is:
SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='#.##0,00 €;-#.##0,00 €';
SET TimeFormat='h:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY h:mm:ss[.fff]';
SET MonthNames='ene;feb;mar;abr;may;jun;jul;ago;sep;oct;nov;dic';
SET DayNames='lun;mar;mié;jue;vie;sáb;dom';
//
T2:
LOAD Field3
FROM
(ooxml, embedded labels, table is Hoja1);
T1:
LOAD Zona,
Field1,
Field2,
Field3,
Num
FROM
(ooxml, embedded labels, table is Hoja1)
Where not Exists(Field3);
thanks in advance
regards
dcd