Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have a field qty_sales (I believe this type of field is called an array) in my Database that returns something like this:
1;2;3;4;5;6;7;8;9;10;11;12
Each of the values represents the qty of a specific product sold in one particular year period (1-12)
how can I load each individual amount as a seperate field in my qlikview script?
i.e: qty_sales_period1, qty_sales_period2, qty_sales_period3 etc...
Thanks in advance...
Fab
Hi In this case you need to use the function subfield() to get the values.
Load SubField(qty,';',1) as qty_sales_period1,
SubField(qty,';',2) as qty_sales_period2........SubField(qty,';',12) as qty_sales_period12
Hi In this case you need to use the function subfield() to get the values.
Load SubField(qty,';',1) as qty_sales_period1,
SubField(qty,';',2) as qty_sales_period2........SubField(qty,';',12) as qty_sales_period12
May be this?
LOAD 'qty_sales_period' & SubField(Field,';') as Field Inline [
Field
1;2;3;4;5;6;7;8;9;10;11;12
];
If needed to call it as single line you can create Concat() using Resident the same table which we need to call Directory name ..
Sample:
LOAD * Inline [
Field
1;2;3;4;5;6;7;8;9;10;11;12
];
Sub_Table:
LOAD 'qty_sales_period' & SubField(Field,';') as Field1 Resident Sample;
DROP Table Sample;
Final:
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:
load
subfield(qty, ';', iterno()) as qty, iterno() as period
from source while iterno() <= substringcount(qty, ';') + 1;
- Marcus
Hi Fabrizio,
may be this
Temp:
LOAD Product,Subfield(qty,';') as qty,'qty_sales_period'&AutoNumber(RowNo(),Product) as period
Inline [
Product,qty
A,1;2;3;4;5;6;7;8;9;10;11;12
B,11;12;13;14;15;16;17;18;19;110;111;112];
Generic LOAD Product,period,qty
Resident Temp;
Drop Table Temp;
Regards,
Antonio
Thanks Guys,
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...
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.
- Marcus
Thanks again Marcus,
I will definitely refer to this information when I get back to working on this report...
Really appreciate your input and everyone else's on this forum, there is no way I could have gotten this far without everyone's contribution...
Thaks a lot...
Fab