Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
patriciousa
Creator II
Creator II

Only load data from a column when equal to specific data in other column?

Hi community.

I have this data loaded:

NameHours
Patrick100
Bob200
John0

I want to insert the "Name" column in a Table but Only when Hours is greater than 0.

Result should be showing only Patrick and Bob.

How do I achieve this?

Thank you.

Regards.

1 Solution

Accepted Solutions
andy_smith2005
Partner
Partner

You wont be able to do this other than from a preloaded file or from a .qvd pre-created file - other than that and much the same as the other post you need to load the data first then select using where statement

Data:
LOAD * INLINE [
Name, Hours
Patrick, 100
Bob, 200
John, 0
]
;

NoConcatenate
DataNew:
load * Resident Data Where Hours > 0;
drop table Data;


You may be able to do this without preloading by using an INPUT box in the load script and associating the input as a variable in the WHERE statement - but you still would need the data pre-loaded or existing as a QVD.

View solution in original post

6 Replies
gsbeaton
Luminary Alumni
Luminary Alumni

Hi Patricio,

If the data is already loaded (ie, it has been executed earlier in the script):

NoConcatenate

NewTable:

Load *

Resident [The Original Table With All Your Data]

Where Hours > 0;

Drop [The Original Table With All Your Data];

You could also put the WHERE clause in your initial load of the data to prevent it being loaded in the first place.

Hope this helps

George

Not applicable

Hi Patricio,

In Table object, add name as dimension.

In the "Field" option, write the following expression : =if(Hours>0,Name).

And unselect the option "Show Null Values".

Regards,

Rohan

andy_smith2005
Partner
Partner

You wont be able to do this other than from a preloaded file or from a .qvd pre-created file - other than that and much the same as the other post you need to load the data first then select using where statement

Data:
LOAD * INLINE [
Name, Hours
Patrick, 100
Bob, 200
John, 0
]
;

NoConcatenate
DataNew:
load * Resident Data Where Hours > 0;
drop table Data;


You may be able to do this without preloading by using an INPUT box in the load script and associating the input as a variable in the WHERE statement - but you still would need the data pre-loaded or existing as a QVD.

View solution in original post

sunny_talwar

Essentially you just need the Where statement which you can use in your FROM or Resident or Preceding load to retrict which doesn't match your requirement.

so as suggested by George add Where Hours > 0; will exclude all places where Hours = 0

patriciousa
Creator II
Creator II
Author

Thank you for you answer, both of you, but where do I put it?

[Planificacion]:

LOAD [Oficina],

  [Empleado ID],

  [Name],

  [Fecha] as "Fecha Registro",

  [Hours] ,

  [Proyecto] as ProyectoPlanif,

  [Descr Proyecto],

  [Cliente],

  [Descr Cliente],

  [Categoria1],

  [Categoria General] as CategoriaGeneralPlanif,

  [Mes] as MesPlanif,

  [Año] as AñoPlanif,

  [Estado Empleado],

  [Mes Estado],

  [Año Estado]

FROM [lib://audit con/archivos plani/Horas pla.xls]

(biff, embedded labels, header is 1 lines, table is sheet1$) Where Hours > 0;

Just to be sure, remember that I what to exclude "Name" when "Hours" > 0

Thank you.

Regards.

patriciousa
Creator II
Creator II
Author

Thank you Andrew.

Regards.