Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
malbordio
New Contributor III

Loading from a excel / txt file a value that I don't want to display on dashboard/query results

Hi,

I am loading some data from another source (or maybe from excel/txt file) (the data are incident reference numbers), but I need to create an exception, that will ignore a specific reference number that I do not want to be included and displayed on my dashboard.

How can I do that while loading the script?

Example:

LOAD

  CycleTimeKey,

  Date(Addmonths([Start Date], $(numMonth))) as [Start Date],

  Date(Addmonths([End Date], $(numMonth))) as [End Date],

  [Number of Transaction Days]

FROM C:\excel.xls,

  except REF123456;

Many thanks!

6 Replies
MVP
MVP

Re: Loading from a excel / txt file a value that I don't want to display on dashboard/query results

LOAD

  CycleTimeKey,

  Date(Addmonths([Start Date], $(numMonth))) as [Start Date],

  Date(Addmonths([End Date], $(numMonth))) as [End Date],

  [Number of Transaction Days]

FROM C:\excel.xls

WHERE REFERENCENUMBER <> 'REF123456';

Replace REFERENCENUMBER with the appropriate field from your excel file.

malbordio
New Contributor III

Re: Loading from a excel / txt file a value that I don't want to display on dashboard/query results

Swuehl,

Thanks for the prompt reply.

I believe that example will work, but not exactly for my doubt. In my case, the reference number information will need to be retrieved from a external file, since access to the qlikfile in the server will be restricted for everyone, and I need people to add those reference numbers in that file, probably placed in a share drive folder, or something.

MVP
MVP

Re: Loading from a excel / txt file a value that I don't want to display on dashboard/query results

EXCLUDE:

LOAD REFNUMBERSTOEXCLUDE FROM External.xls;

LOAD

  CycleTimeKey,

  Date(Addmonths([Start Date], $(numMonth))) as [Start Date],

  Date(Addmonths([End Date], $(numMonth))) as [End Date],

  [Number of Transaction Days]

FROM C:\excel.xls

WHERE NOT EXISTS( REFNUMBERSTOEXCLUDE, REFERENCENUMBER);

DROP TABLE EXCLUDE;

malbordio
New Contributor III

Re: Loading from a excel / txt file a value that I don't want to display on dashboard/query results

Maybe I am doing it wrong, but I'm not there yet.

Could you please reply with the exact script based on the following, please?

I am loading an external file called exclude.xls

exclude.xls.jpg

So, on Qlikview, I have this reference and other references like this. Those references where loaded in another script, but now I want to run a script that excludes one or more references, for example, the one I listed on this excel, or in the future other ones I will include on the same excel file.

How should be compiled the script to run exactly like this?

Many thanks again.


Regards,

MVP
MVP

Re: Loading from a excel / txt file a value that I don't want to display on dashboard/query results

To create an 'exact script', I would need to know your input sources in detail.

Basically my above sample should work.

First load your excel file with the values to exclude (use the table wizard in the script editor to load the file, make sure that you get a table back with field 'exclude' and the one value you have input 'INC514077').

EXCLUDE:

LOAD exclude FROM exclude.xls

(FormatSpecificationsComeHere-UseTheTableWizardIfNeeded);

RESULT:

LOAD

  CycleTimeKey,

  Date(Addmonths([Start Date], $(numMonth))) as [Start Date],

  Date(Addmonths([End Date], $(numMonth))) as [End Date],

  [Number of Transaction Days]

FROM C:\excel.xls

(AgainFormatSpecificationsComeHere)

WHERE NOT EXISTS( exclude, REFERENCENUMBER);

DROP TABLE EXCLUDE;

Still you would need to replace REFERENCENUMBER with the field from your input table that contains the reference number you want to check for exclusion.

If this still does not help you, please post your two excel files (or sample files with the same structure and dummy data).

malbordio
New Contributor III

Re: Re: Loading from a excel / txt file a value that I don't want to display on dashboard/query results

So far I was able to load the number/incident ID I wanted from excel:

LOAD exclude

FROM

(biff, embedded labels, table is REFERENCENUMBER$);

Still managing to execute properly the second part, to, in fact, exclude the reference itself.

D:\exclude.xls

tab: REFERENCENUMBER

column A

A1 exclude

A2 INC514592

Still trying

Community Browser