10 Replies Latest reply: Dec 2, 2010 1:21 PM by Gordon Savage RSS

    Loading in CSV

    Chris Hopkins

      Hi, I don't think this is possible but i thought i'd ask the question.

      I have thousands of stock REASON DESCRIPTIONS. an example of some descriptions are:
      STOCK CHECK
      P/O
      ORDERING
      etc

      but the problem is that the description would have a date and time after it, e.g.:
      STOCK CHECK 10/11/10 15:30:01
      This results in thousands of description starting with STOCK CHECKING. I am trying to create a table with just an abreviation of the REASON DESCRIPTION which will then display anything relating to it.

      e.g,

      New Table:
      Stock Check
      Price Over
      Ordering Adjustment

      When 'Stock Check' is selected, All results relating to the REASONDESCRIPTIONS 'STOCK CHECK ....' will be displayed, instead of having to highlight ALL STOCK CHECK descriptions.
      The easiest way i thought to do this would be to load in a CSV.

      Question 1: Is this the easiest way?
      Question 2: Can i use something like a WILDCARD function in the CSV?

      What i was thinking is i could list all of my abreviations in a CSV with a wildcard next to it:
      e.g.

      error loading image

       

      Does this make sense? is it possible?

      Thanks

        • Loading in CSV
          Gordon Savage

          how about something like:

          Load ..

          REASONDESCRIPTION,

          left(REASONDESCRIPTION, len(REASONDESCRIPTION)-18) as LISTBOX

          which assuming the last 18 chars in REASONDESCRIPTION are always the date/time will return all characters before that into a field LISTBOX which you can use for selections.

          Regards,

          Gordon

            • Loading in CSV
              Chris Hopkins

              Thanks for your reply Gordon,

              Although that won't really work, as some of the descriptions have date & time, Some have just date, some have user & date & time, etc

              which is why i thought about using a wildcard function for the first few letters. I know i can do this in QLIKVIEW, but i have been told that using too many IF functions in the script will seriously effect the performance of the server...

            • Loading in CSV
              Charlotte Beattie

              Could you use a wildcard in an inline table instead of in a csv and if statement?

                • Loading in CSV
                  Chris Hopkins

                  Thanks Charlotte,

                  I have used IF statemenst now in the script which seems to work as i wanted.

                   

                  Can anyone tell me if 33 IF statements in the script will effect the load performance much?

                    • Loading in CSV
                      Gordon Savage

                      It all depends on how much data you have but I would think the answer must be yes either way. Its always a question of whether the performance is acceptable or not...

                      One way that has occured to me is using wildmatch; try the following code:

                      Mappings:
                      LOAD * INLINE [
                      Dummy, MapTo
                      1, Stock*
                      1, Order*
                      1, Price override*
                      ];

                      [tMap String]:
                      LOAD concat(chr(39) & MapTo & chr(39), ',') as WildQuoted,
                      concat(MapTo, ',') as WildUnquoted
                      RESIDENT Mappings
                      GROUP BY Dummy;

                      LET vWildQuoted = peek('WildQuoted', 0);
                      LET vWildUnquoted = peek('WildUnquoted', 0);

                      DROP TABLE [tMap String];

                      [tData]:
                      // This is your incoming data
                      LOAD * INLINE [
                      InData
                      Stock data
                      Stock Ordering 30/11/10
                      Stock Orders Gordon
                      Price override today
                      Orders 30/11/2010 09:11:00
                      Price override
                      Something odd
                      ];

                      [Data]:
                      Load InData,
                      if (MapMatch = 0, InData, pick(MapMatch, $(vWildQuoted))) as MapTo;
                      LOAD InData,
                      wildmatch(InData, $(vWildQuoted)) as MapMatch
                      RESIDENT tData;

                      DROP TABLE tData;

                      I would expect it to be faster with lots of rows, but if nothing else may make maintenance easier as you could put the inline 'Mappings' table into a spreadsheet.

                      Hope this helps,

                      Gordon

                        • Loading in CSV
                          Gordon Savage

                          ...you can get rid of the references to WildUnquoted which are spurious (was just playing with different ideas)

                            • Loading in CSV
                              Chris Hopkins

                              Hi Gordon,

                              Thanks for your reply, I'm not completely understanding the code though. If i post a sample of my script, could you possibly use my fields?

                              or maybe just explain what is what?

                              Any help is appreciated.

                              thanks

                              Chris

                                • Loading in CSV
                                  Gordon Savage

                                  Just copy and paste the code into a new test document and reload it. Add table objects and list boxes and you should be able to see what it does quite easily.

                                  Regards,

                                  Gordon

                                    • Loading in CSV
                                      Chris Hopkins

                                      Hi Gordon,

                                      I can see how it works now, although i'm a bit lost...

                                      My data is in a table called 'StoreStockMovement' and a field named narrative.

                                      How would i change the script to match my table and field?

                                      [Data]:
                                      Load InData,
                                      if (MapMatch = 0, InData, pick(MapMatch, $(vWildQuoted))) as MapTo;
                                      LOAD InData,
                                      wildmatch(InData, $(vWildQuoted)) as MapMatch
                                      RESIDENT tData;

                                      DROP TABLE tData;

                                       

                                        • Loading in CSV
                                          Gordon Savage

                                          The 'Mappings' table reflects the strings you want to match with the wildcard '*'. For example, 'Stock*' is used to map anything beginning with 'Stock' to the 'MapTo' field value of 'Stock*'. This table you could maintain as an xls/csv etc. The filed 'Dummy' doesnt need to be in the external source but is used just to be able to use a 'Group By' in the temporary table 'tMap String'.
                                          The 'LET vWildQuoted = ...' fetches the first (and only) value (into variable vWildQuoted) of the field 'WildQuoted' from table 'tMap String' and so contains all the values of 'MapTo' in a string with each one between single quotes and seperated by a ',' (ie 'Stock*','Order*','Price override')
                                          Tables 'tData' and 'Data' represents your table 'StoreStockMovement' where the field 'InData' represents your field 'narrative'.
                                          The 'wildmatch(...' looks for a wildcard match of 'InData' with the string in vWildQuoted and if so returns a number as to its matching position in the string as the field 'MapMatch'. 'MapMatch' will be 0 if there was no match and so the preceding load uses 'if (MapMatch = 0...)' to use the full text else returns the wildcard string it matched on as the 'key field' back to table 'Mappings'.

                                          Hope this helps!

                                          Gordon