topic Loading in CSV in QlikView

Loading in CSV

hopkinsc — Tue, 30 Nov 2010 15:54:53 GMT

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.

Does this make sense? is it possible?

Thanks

Loading in CSV

Tue, 30 Nov 2010 16:13:26 GMT

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

hopkinsc — Tue, 30 Nov 2010 16:19:52 GMT

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_qvw — Tue, 30 Nov 2010 16:41:42 GMT

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

Loading in CSV

hopkinsc — Tue, 30 Nov 2010 16:58:33 GMT

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

Tue, 30 Nov 2010 18:45:47 GMT

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

Tue, 30 Nov 2010 18:49:29 GMT

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

Loading in CSV

hopkinsc — Thu, 02 Dec 2010 16:28:24 GMT

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

Thu, 02 Dec 2010 17:11:08 GMT

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

hopkinsc — Thu, 02 Dec 2010 17:31:30 GMT

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

Thu, 02 Dec 2010 18:21:42 GMT

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