Qlik Community

Ask a Question

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Creator II
Creator II

Yikes, Falling over on a Where!

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!

Di

6 Replies
Employee
Employee

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 ?

Creator
Creator

maybe with:

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

Creator II
Creator II

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!

Creator II
Creator II

what happens with:

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

Creator
Creator

or

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

Partner
Partner

> 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.