topic Re: LOAD ... Where Not Exists in QlikView

LOAD ... Where Not Exists

Thu, 27 Mar 2014 13:07:48 GMT

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?

Re: LOAD ... Where Not Exists

MK_QSL — Thu, 27 Mar 2014 13:10:08 GMT

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

PrashantSangle — Thu, 27 Mar 2014 13:15:44 GMT

Hi,

Load *

from table name

where ThirdFieldName <> 'E';

Regards,

Re: LOAD ... Where Not Exists

lironbaram — Thu, 27 Mar 2014 13:22:04 GMT

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

Re: LOAD ... Where Not Exists

Thu, 27 Mar 2014 13:26:25 GMT

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.

Re: LOAD ... Where Not Exists

arsal_90 — Thu, 27 Mar 2014 13:49:44 GMT

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

Re: LOAD ... Where Not Exists

Thu, 27 Mar 2014 14:54:50 GMT

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?