Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Not applicable

LOAD ... Where Not Exists

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?

Bsp.jpg

6 Replies
MVP
MVP

Re: LOAD ... Where Not Exists

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;

Re: LOAD ... Where Not Exists

Hi,

Load *

from table name

where ThirdFieldName <> 'E';

Regards,

PS

Great dreamer's dreams never fulfilled, they are always transcended.
Partner
Partner

Re: LOAD ... Where Not Exists

//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)

Not applicable

Re: LOAD ... Where Not Exists

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.

Highlighted
arsal_90
Contributor III

Re: LOAD ... Where Not Exists

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

Not applicable

Re: LOAD ... Where Not Exists

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?