topic Re: tough task? in QlikView

tough task?

Sat, 18 Oct 2014 14:34:13 GMT

Hi experts,

Date Format should be DD/MM/YYYY. Suppose there are n rows where row 2 and row 4 have different date formats

Row1   12/10/2014
Row2   12-10-2014
Row3   12/10/2014
Row4   12-10-2014
.         .
.
.
.
Rown    DD/MM/YYYY

During load I want to eliminate the rows with erroneous date format and convert into qvd. at the same time i want all the erroneous rows to be stored into a QVD called Error.Qvd.

how to store non erroneous dates in one qvd and at same time how to create qvd for erroneous data?

is it possible to create 2 qvds at a time ?

Re: tough task?

antoniotiman — Sat, 18 Oct 2014 15:03:13 GMT

Hi,

Try

Load *

Where alt(Date#(Date,'DD/MM/YYYY'),0) > 0;

Load *

from Table;

You load only correct date.

Regards,

Antonio

Re: tough task?

Sat, 18 Oct 2014 15:13:23 GMT

for wrong date i need to create?

Re: tough task?

MarcoWedel — Sat, 18 Oct 2014 15:13:46 GMT

Hi,

one solution:

QVD correct dates

QVD incorrect dates

tabSource:
LOAD * INLINE [
    Row, Date
    Row1, 12/10/2014
    Row2, 12-10-2014
    Row3, 12/10/2014
    Row4, 12-10-2014
];

NoConcatenate

tabCorrectDates:
LOAD *
Resident tabSource
Where IsNum(Date);

NoConcatenate

tabIncorrectDates:
LOAD *
Resident tabSource
Where not IsNum(Date);

DROP Table tabSource;

STORE tabCorrectDates into tabCorrectDates.qvd (qvd);

STORE tabIncorrectDates into tabIncorrectDates.qvd (qvd);

hope this helps

regarsd

Marco

Re: tough task?

antoniotiman — Sat, 18 Oct 2014 15:15:24 GMT

For wrong date use

Where alt(..............) = 0;

Re: tough task?

veidlburkhard — Sat, 18 Oct 2014 15:19:42 GMT

Hi manojqlik,

this Where clause gives you the incorrect data formats:

Load *

Where alt(Date#(Date,'DD/MM/YYYY'),0) = 0;

Load *

from Table;

(see QV Help, 'Conditional Functions', 'Alt')

Hope this helps

Burkhard

Re: tough task?

Sat, 18 Oct 2014 15:25:39 GMT

Hi Manoj.

Does this attachment help answer your question?

Regards

Re: tough task?

Sat, 18 Oct 2014 16:19:23 GMT

Hello manoj ,

Try like this