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

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

Anonymous — Sat, 07 Jun 2014 10:52:39 GMT

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!

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

swuehl — Sat, 07 Jun 2014 10:54:30 GMT

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.

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

Anonymous — Sat, 07 Jun 2014 11:03:38 GMT

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.

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

swuehl — Sat, 07 Jun 2014 11:08:15 GMT

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;

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

Anonymous — Sat, 07 Jun 2014 17:53:28 GMT

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

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,

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

swuehl — Sun, 08 Jun 2014 11:30:46 GMT

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

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

Anonymous — Mon, 09 Jun 2014 20:57:44 GMT

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