Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
fgiorgio
Contributor

Loading an Array field in qlikview

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

1 Solution

Accepted Solutions
techvarun
Valued Contributor II

Re: Loading an Array field in qlikview

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

9 Replies
techvarun
Valued Contributor II

Re: Loading an Array field in qlikview

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

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

];

Life is so rich, and we need to respect to the life !!!

Re: Loading an Array field in qlikview

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;

Life is so rich, and we need to respect to the life !!!

Re: Loading an Array field in qlikview

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

antoniotiman
Honored Contributor III

Re: Loading an Array field in qlikview

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

fgiorgio
Contributor

Re: Loading an Array field in qlikview

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

fgiorgio
Contributor

Re: Loading an Array field in qlikview

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

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

fgiorgio
Contributor

Re: Loading an Array field in qlikview

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

Community Browser