9 Replies Latest reply: Oct 25, 2010 10:49 AM by Roland Kunle RSS

    Help with Load Script

    Chris Hopkins

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



        • Help with Load Script
          Miguel Angel Baeyens de Arce

           


          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.

            • Help with Load Script
              Chris Hopkins

              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?



                • Help with Load Script
                  Miguel Angel Baeyens de Arce

                  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.

                    • Help with Load Script
                      Chris Hopkins

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

                      • Help with Load Script
                        Chris Hopkins

                        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?