Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
swuehl
MVP
MVP

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.

Anonymous
Not applicable
Author

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.

swuehl
MVP
MVP

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;

Anonymous
Not applicable
Author

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,

swuehl
MVP
MVP

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

Anonymous
Not applicable
Author

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