Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
Specialist II
Specialist II

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

View solution in original post

9 Replies
techvarun
Specialist II
Specialist II

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

Anil_Babu_Samineni

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

];

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Anil_Babu_Samineni

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;

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
marcus_sommer

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
Master III
Master III

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

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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

marcus_sommer

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

Anonymous
Not applicable
Author

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