LOAD ... Where Not Exists

[#1]

Hi I am searching for a solution for following problem.

I try to load only data where the 3rd field ist always F. If there is an entry with E it should skip all values for the material.

So in my example picutere i will only have material 1,2 and 5.

3 should be skipped because it is allways E (thats a simple where clause)

and 4 should be also skipped because one of the entrys is E

How can i do it?

[MK_QSL]

Load *

from table name

where ThirdFieldName = 'F';

UPDATE :

Temp:

Load * Inline

[

  F1, F2, F3

  1,A,F

  2,B,F

  3,A,E

  4,A,E

  4,B,F

  5,A,F

  5,B,F

];

Join

Load F1, MinString(F3) as MinFlag Resident Temp Group By F1;

NoConcatenate

Final:

Load F1, F2, F3

Resident Temp

Where MinFlag = 'F';

Drop Table Temp;

[PrashantSangle]

Hi,

Load *

from table name

where ThirdFieldName <> 'E';

Regards,

PS

[lironbaram]

//find the metrials to load

metrials_to_load:

load distinct material As CheckValues

from XXX

where ThirdFieldName<>'F'

///load data

Data:

load *

from XXX

where not exists(CheckValues,material)

[#5]

Hi,

I understand you wanna keep data from original table where FirstFieldName has only entries with 'F' in original table.

Here's a way:

Filtered_Table:

Load *

from table name

;

Left Join(Filtered_Table)

Load Distinct FirstFieldName, 'X' as Flag_Not_F

from table name

Where ThirdFieldName <> 'F'

;

Inner Join(Filtered_Table)

Load Distinct FirstFieldName

Resident Filtered_Table

Where IsNull(Flag_Not_F)

;

Drop Field Flag_Not_F;

Hope this helps.

[arsal_90]

Please find the attached file and let me know if u have any problem

[#7]

Ok I think we found some solutions. Give me some time to try them out and find out the best for me.

Two of them are using the MinString Funktion.

Are these solutions also working with other scenarios or only with E and F because E is alphabeticly smaller than F?