topic Re: where clause in QlikView

where clause

Tue, 05 Jan 2016 14:17:18 GMT

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

Re: where clause

rubenmarin — Tue, 05 Jan 2016 14:24:22 GMT

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 '??/??/????';

Re: where clause

Tue, 05 Jan 2016 14:28:25 GMT

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

Re: where clause

rubenmarin — Tue, 05 Jan 2016 14:37:40 GMT

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 '??/??/????';

Re: where clause

Tue, 05 Jan 2016 14:47:14 GMT

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

Re: where clause

Tue, 05 Jan 2016 14:57:19 GMT

I got it correct ....

Thanks a million much appreciated