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

[patriciousa]

Hi community.

I have this data loaded:

Name	Hours
Patrick	100
Bob	200
John	0

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.

[gsbeaton]

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

[#3]

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 · Accepted solution]

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.

[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]

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]

Thank you Andrew.

Regards.