topic Re: Yikes, Falling over on a Where! in QlikView

Yikes, Falling over on a Where!

dinicholls — Fri, 26 Sep 2014 15:22:03 GMT

Hi,

I have the following script, (cut down a bit for this example!):

LOAD id,

[Company Number],

Debtor_status_id,

amount_invoice,

[DOC ID],

[Invoice Number2],

invoice_date,

amount_open,

code,

[Dispute Description],

query_date,

[Days In Dispute],

[Query Status Definition],

[Last Contact By],

invoice_number

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 2 lines)

WHERE([Company Number] = '29' And

code <> Q16 And

code <> Q17 And

code <> Q18 And

code <> Q19 And

code <> Q21 And

code <> Q23 And

code <> Q24 And

code <> Q25 And

code <> Q26 And

code <> Q27 And

code <> Q28 And

code <> Q29 );

My problem occurs where there aren't any Q16's or Q27's etc in the CSV file.

How do I get it to skip past it that 'Q' code if there are none in the file, and continue loading the rest of the file?

Thanks for all you help, I'm still learning QlikView! Its a change from excel and crystal!

Re: Yikes, Falling over on a Where!

JonnyPoole — Fri, 26 Sep 2014 15:25:21 GMT

The Q* fields seem like a good candidate for a CROSSTABLE load. Then you can do the filter very easily because all the Q values go into 1 field.

Are you familiar with crosstable ?

Re: Yikes, Falling over on a Where!

demoustier — Fri, 26 Sep 2014 15:28:10 GMT

maybe with:

where( [Company Number] = '29' And no wildmatch(code ,'Q16','Q17,.....and so on)

Re: Yikes, Falling over on a Where!

dinicholls — Fri, 26 Sep 2014 15:38:13 GMT

Hi,

Can't you a cross-tab as this is just one of many other tables / CSV files that have to be loaded into my app to form a report.

Demoustier, I've tried that way, and for some reason it keeps falling over on me!

Re: Yikes, Falling over on a Where!

tcullinane — Fri, 26 Sep 2014 15:54:52 GMT

what happens with:

where([Company Number]='29' AND (code<>'Q16' OR code<>'Q17' Or ...etc...) )

Re: Yikes, Falling over on a Where!

demoustier — Fri, 26 Sep 2014 16:01:49 GMT

where([Company Number]='29') unless (code='Q16' OR code='Q17' Or ...etc...) )

Re: Yikes, Falling over on a Where!

hewemel1 — Fri, 26 Sep 2014 16:10:37 GMT

> How do I get it to skip past it that 'Q' code if there are none in the file, and continue loading the rest of the file?

Your code looks fine. What exactly goes wrong? Can you provide example data?

The solution of Demoustier ist a better way to write your code, but at the end, it will do the same. If there is a problem with your code, it will still be there with this code.

The solution of Thomas Cullinane is also fine. It is not shorter, but also ok. But still, it does the same.