If needed to call it as single line you can create Concat() using Resident the same table which we need to call Directory name ..
LOAD * Inline [
LOAD 'qty_sales_period' & SubField(Field,';') as Field1 Resident Sample;
DROP Table Sample;
LOAD Concat(Field1, ';') as Field2 Resident Sub_Table;
You could do it with the below mentioned subfield() function as a loop but I would do it within a while-loop and creating a "normal" table with an amount-field and a period field and not a crosstable. I mean something like this:
subfield(qty, ';', iterno()) as qty, iterno() as period
from source while iterno() <= substringcount(qty, ';') + 1;
This is great Marcus,
you went over and above hat was asked as I have only just realised that i also need a Period number... for further calculations...
(next step is I need to add up the previous 12 periods as one field and the previous 3 periods as one field...
I still have to get my head around this one,
but this is a great suggestion...
Thanks for sharing your knowledge...
Cheers - Fab
In this case I would create a real date from the period probably with:
makedate(Year /* somewhere you need to have this information */, period, 1) as Date
and this date would be linked to a master-calendar and most of the advanced date-fields like YTD, LYTD and so on would be flagged there. Here you will find a collection of valuable links to this topic: How to use - Master-Calendar and Date-Values.
some great input here...
I appreciate everyone's advice!!
loading a table using the subfield function worked a treat...
I wish I could make them all correct!! but i can only choose one correct - I will give this to the first correct answer and mark the others as helpful.
Thanks again to everyone!!
onto the next qlikview problem...