Using FieldName in a Where Clause

[eduardo_dimperio]

Hi,

I've this situation

Input:

LOAD

//more fields before

'42990'

'42991'

'42992'

'42993'

'42994'

'42995'

'42996'

'42997'

'42998'

'42999'

//more fields after

FROM [lib://VisitaMI (bi_bi.desenvolvimento)/CONTROLE - AGENDAMENTO -2016-2017-2018 V012.xlsx]

(ooxml, embedded labels, table is Geral);

This came from a Excel file where each field is a date.

How can i select only the field where field's name ate equal today? (42998)

I thought something like :

LOAD

*

FROM [lib://VisitaMI (bi_bi.desenvolvimento)/CONTROLE - AGENDAMENTO -2016-2017-2018 V012.xlsx]

(ooxml, embedded labels, table is Geral)

WHERE FieldName() = Num(Today());

Any Help Please

[Clever_Anjos]

It´s not possible, you have to use the correct field name

One alternative is cross table your excel file and then filter the results

[swuehl · Accepted solution]

Maybe like this

Let vField = Num(Today(1));

LOAD

    //more fields before

   "$(vField)",

   //more fields after

FROM ...; // NO WHERE CLAUSE

[eduardo_dimperio]

Hi ,

Just to Know i solve using crosstable, that way i could create a Field named "Data" with all field's name on it and filter

Teste:

Crosstable (Data, Visita)

LOAD

*

FROM [lib://VisitaMI (bi_bi.desenvolvimento)/CONTROLE - AGENDAMENTO -2016-2017-2018 V012.xlsx]

(ooxml, embedded labels, table is Geral);

NoConcatenate

Teste2:

Load

*

resident Teste

WHERE Data=Num(Today());

drop table Teste;

exit script;

[eduardo_dimperio]

Hi Clever,

I used crosstable on my script, cause other person where use this excel file

Valeu.

[vvira1316]

Hi,

Will you be able to share a sample data set. It can be limited records with this field and may be some other fields. That will help to guide you.

BR,

Vijay

[eduardo_dimperio]

Hi Stefan, always helping us

I think using crosstable apears to be easier to work with, nevertheless your answer works just fine

Thank you

[eduardo_dimperio]

Hi Vijay !

Thank you for your time, but i already solve with crosstable and Stefan's answer works very weel too.

[swuehl]

You are welcome.

Might be a good idea to alias the field, so you don't have to change the script / expressions:

  "$(vField)" as TodaysField,