Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

patriciousa
Contributor

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
New Contributor III

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

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.

6 Replies
gsbeaton
Contributor II

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

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

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

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
New Contributor III

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

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.

MVP
MVP

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

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
Contributor

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

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
Contributor

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

Thank you Andrew.

Regards.

Community Browser