Loading in CSV

[hopkinsc]

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

[#2]

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

[hopkinsc]

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

[charlotte_qvw]

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

[hopkinsc]

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?

[#6]

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

[#7]

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

[hopkinsc]

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

[#9]

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

[hopkinsc]

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;