where clause

[#1]

Hi there,

i have my 'item' numbers in different formats like - 15-09-0480 , 15/11/4335 and some in alphabets like P16043 , CDESSK etc..

I want to to write a where clause which takes only date formats.. like these two - 15-09-0480 , 15/11/4335..

InvoiceRef:

LOAD

  "item" as journal_number,

  "refernce"

FROM

[QVDs\InvoiceRefTable.qvd]

(qvd)

Where "item" like '??-??-????' or '??/??/????';

Journals:

LOAD

"journal_number"

FROM

[QVDs\TempJournals_D.qvd]

(qvd);

Thats my expression... Still it isnt working ... could anyone help me ?

Thanks indeed

[rubenmarin]

Hi vidisha, I tried with this script that seems to work:

Orig:

LOAD * Inline [

DataOrig

15-09-0480

15/11/4335

45/3645645

];

Result:

LOAD Text(DataOrig) as Data

Resident Orig where DataOrig like '??-??-????' or DataOrig like '??/??/????';

[#3]

Hey Ruben,

Thank much for the reply..

But could you tell me how can i change that in my script ?

Also the 'refernce' numbers are in negative , i want to multiply them by -1 to change that to positive. Could you suggest me the same please ?

Thank you

[rubenmarin]

Hope this helps:

InvoiceRef:

LOAD

  "item" as journal_number,

  "refernce"*-1 as refernce //you can also use fabs("refernce") as refernce, fabs() returns absolute number

FROM

[QVDs\InvoiceRefTable.qvd]

(qvd) Where "item" like '??-??-????' or "item" like '??/??/????';

[#5]

Hey Ruben

Thanks a million for helping..

i tried the same,

InvoiceRef:

LOAD

  "item" as journal_number,

  "refernce"*-1 as refernce

FROM

[QVDs\InvoiceRefTable.qvd]

(qvd)

Where "item" like '??-??-????' or "item" like '??/??/????';

Journals:

LOAD

"journal_number"

FROM

[QVDs\TempJournals_D.qvd]

(qvd);

i reloaded.... Still shows the alphabets in items and negative numbers in refernce

[#6]

I got it correct ....

Thanks a million much appreciated