topic Re: Loading an Array field in qlikview in QlikView

Loading an Array field in qlikview

Anonymous — Thu, 12 Oct 2017 05:57:50 GMT

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

Re: Loading an Array field in qlikview

techvarun — Thu, 12 Oct 2017 06:04:49 GMT

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

Re: Loading an Array field in qlikview

Anil_Babu_Samineni — Thu, 12 Oct 2017 06:06:41 GMT

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

];

Re: Loading an Array field in qlikview

Anil_Babu_Samineni — Thu, 12 Oct 2017 06:07:19 GMT

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;

Re: Loading an Array field in qlikview

marcus_sommer — Thu, 12 Oct 2017 07:35:02 GMT

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

Re: Loading an Array field in qlikview

antoniotiman — Thu, 12 Oct 2017 09:12:08 GMT

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

Re: Loading an Array field in qlikview

Anonymous — Mon, 16 Oct 2017 00:50:11 GMT

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

Re: Loading an Array field in qlikview

Anonymous — Mon, 16 Oct 2017 00:56:13 GMT

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

Re: Loading an Array field in qlikview

marcus_sommer — Mon, 16 Oct 2017 06:45:33 GMT

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

Re: Loading an Array field in qlikview

Anonymous — Mon, 16 Oct 2017 21:41:33 GMT

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