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: 
hopkinsc
Partner - Specialist III
Partner - Specialist III

Help with Load Script

Hi, i am writing a stock application to report on REDUCTIONS and WASTE. The problem is the file that we have to use which comes from all our retail stores includes other stock reasons such as GOODS RECEIVED, STOCK TRANSFERS, DELETED PRODUCTS, STOCK CHECKING etc...

Attached is an example of the CSV that we load in which includes 1 of each of the reason codes...

What i want to do is only load in the reasons related to REDUCTIONS and WASTE. So i either want to write in the load script to exclude all others or to only include a,b,c for example.

my questions are:

  • Is there a way to exclude items in the script. I know how to include things by using the WHERE command, but have never tried excluding.
  • In the CSV example attached, you can see a reason: GOODS RECEIVED, RL1000,Store, 05:30. As this reason includes a time, there could be thousands of these lines. In DOS, i would use a command *.* to include all lines, (e.g. dir goods*.*), which would then list all lines starting with the word goods. Does QLIKVIEW have a function like this?

Or does anyone have any other ideas on how to only load in a a handful of reasons.

Here is my Load Script



StoreStockMovement:

LOAD StoreNumber,

StoreDate,

[Stock Movement Reason Code],

ProductCode,

EANCode AS EAN,

IssueNumber,

CommodityGroup,

EmployeeId,

Narrative,

StockQty,

StockValue,

LinesAffected,

CostValueDiff,

RSPValueDiff,

StoreTime,

%LkStoreReasonCode

FROM

..\..\..\Dump\Qlikview\Retail\RetailDaily\TMPstorestockmovement.qvd

(qvd);



9 Replies
Miguel_Angel_Baeyens


hopkinsc wrote:Is there a way to exclude items in the script. I know how to include things by using the WHERE command, but have never tried excluding.


There are several ways of doing this. I usually use MATCH or EXISTS when you cannot do a SQL statement (so it seems the case) to get from the data source the values you want. The script would look like this

ReasonsToLoad:LOAD * INLINE [ReasonCodeWASTEREDUCTIONS]; Data:LOAD *FROM file.qvd(qvd)WHERE EXISTS(ReasonCode); // This field must be the same as the preivously loaded inline DROP TABLE ReasonsToLoad;



hopkinsc wrote:In the CSV example attached, you can see a reason: GOODS RECEIVED, RL1000,Store, 05:30. As this reason includes a time, there could be thousands of these lines. In DOS, i would use a command *.* to include all lines, (e.g. dir goods*.*), which would then list all lines starting with the word goods. Does QLIKVIEW have a function like this?


I understand that, once you have your data loaded, you want a way to select some values that are going to be displayed in one chart. If you liked to load all values, you would use "ReasonCode" as dimension. But since you only want to load some of the dimensions, creating a calculated dimension in your chart as

=If(Left(ReasonCode, 4) = 'Good', ReasonCode)


And check "Suppres null values" for that dimension, you will gett all possible values for ReasonCode which 4 first chars are "Good".

From this starting point to whatever you can imagine.

Hope that helps.

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

Hi Miguel,

Thanks for your reply. The last part of your reply

=IF(LEFT(REASONCODE, 4)='GOOD',REASONCODE)

that displays everything starting with GOOD... Is there a similar way to exclude everything starting with GOOD?



Miguel_Angel_Baeyens

Hi,

I'd say there are at least two:

=IF(LEFT(REASONCODE, 4) <> 'GOOD', REASONCODE)


and

=IF(LEFT(REASONCODE, 4) = 'GOOD', null(), REASONCODE)


I don't know which one would perform better in large tables or with complex expressions.

Regards.

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

Hi, Sorry to bother you again Miguel...

Both suggestions seem to work, but all they seem to do is stop the reasoncode from displaying. The rest of the imformation appear. I want to be able to exclude the full line.

eg.

StoreNumber, Date, ProductCode, Reason, RSP etc.

with the calculated dimension you suggested, all other details are still visable which obviously means when i try and calculate total RSP for example, the calculation will include reasons that i don't want it to include.

I know i can write the calculation to ignore the reasons i don't want, but i don't really want the end user to see ANY details relating to anything other than waste and reductions.

Is there a way i can stop the full line being displayed relating to reasons that i'm not interested in.?

Many thanks for your help..

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

Hi Miguel.

I have added a calculated dimension

=IF(Left(Narrative, 4) = 'Good' ,null(), Narrative)

which excludes anything starting with 'Good'. I can do this for all reasons that i do not want to see, but how do i add more than 1 in the same calculated dimension.

I have tried

=IF(Left(Narrative, 4) = 'Good' ,null(), Narrative) and IF(Left(Narrative, 4) = 'Stock' ,null(), Narrative)

but it doesn't seem to work.

I would probably need to add around 10 reasons in the same calculated dimension

Any ideas?

Not applicable

Hi,

I would try this (syntax and usage of and/or)


=IF(Left(Narrative, 4) = 'Good' or Left(Narrative, 4) = 'Stock' , null(), Narrative)


hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

Hi Kurokarl,

Thanks for your reply but it didn't work.

I tried both and / Or but no luck.

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

I just realised you changed the position of null(), it works great now 🙂 thanks..

Not applicable

OK,

now you have one if-statement with several conditions, ( one (!) Statement). In your old sentence there have been two if-Statements. This won't work.

Regards, Roland