topic Re: Splitting a table during load? in QlikView

Splitting a table during load?

Thu, 21 May 2015 09:15:36 GMT

Hi, I am quite new to Qlik (Sense) and am wondering if there is an easier way to "vertically" split a table.

The situation I am in, is:

I have got tables that contain data for several different products in different columns.

colA1, ..., colAn, colB1, ..., colBn, colC1, ..., colC3,...

Where colA1, ... contain data for product A

Where colB1, ... contain data for product B

and so on

So in order to do some of my reporting I have split the tables like this

LOAD

columns for prod A

WHERE

columnA > 0

;

LOAD

columns for prod B

WHERE

columnB > 0

;

...

Which results in the original table being read several times during load. Is there any known way to have the split in one go?

Any suggestions or hints are appreciated

Ralph

Re: Splitting a table during load?

Gysbert_Wassenaar — Thu, 21 May 2015 16:24:24 GMT

Nope, one load statement can generate only one table.

Re: Splitting a table during load?

petter — Thu, 21 May 2015 17:23:28 GMT

‌Is it the speed that concerns you or is it the number of load statements that is cumbersome? Both can be addressed. Not completely but quite significantly.

Re: Splitting a table during load?

sunny_talwar — Thu, 21 May 2015 17:29:45 GMT

I have an example which you might find useful.

Please have a look and see if this helps.

Best,

Sunny

Re: Splitting a table during load?

petter — Thu, 21 May 2015 17:30:05 GMT

‌Actually you gain very little by splitting one table into many. The way Qlik Sense works with its associative database and de-duplication (compression) it will be more efficient and speedier in keeping it in one table. So why do you need to split it in the first place?

Re: Splitting a table during load?

sunny_talwar — Thu, 21 May 2015 17:31:28 GMT

I totally forgot this was Qlik Sense discussion. But the idea might still work. Let me paste the script here:

Table:

CrossTable(Measurement, Data, 3)

LOAD Dim1,

Dim2,

Dim3,

[Price 1] & '|' & [Quantity 1],

[Price 2] & '|' & [Quantity 2]

FROM

Crosstable.xlsx

(ooxml, embedded labels, table is Sheet1);

Data:

LOAD Dim1,

Dim2,

Dim3,

Replace(PurgeChar(SubField(Measurement, '|', 1), '[]'), ' & ' & Chr(39), '') as PriceType,

Replace(PurgeChar(SubField(Measurement, '&', -1), '[]'), ' & ' & Chr(39), '') as QuantityType,

SubField(Data, '|', 1) as Price,

SubField(Data, '|', 2) as Quantity

Resident Table;

DROP Table Table;

Re: Splitting a table during load?

JonnyPoole — Thu, 21 May 2015 17:43:58 GMT

I think you are referring to a crosstable load, that pivots the column values into a single field.

You can do it and Michael made a helpful video on it for qlik sense users:

Power of Qlik Script - Reshaping Data with Crosstable (video)

Re: Splitting a table during load?

Fri, 22 May 2015 15:31:23 GMT

Hi Petter,

it's not the load of the table itself, that is bothering me.

Later on in the script I do some calculations which are different dependent on the product and each record can contain info on each of the products. So I have to:

For every record check if it contains product A data; then do this

For every record check if it contains product B data; then do that

and so on

I think I already gained some speed because I decided to split the table and have to do the checking only once instead of several times (and by reducing the amount rows that I have to read not even containing any relevant data).

I was just wondering if I could gain some more speed, by being able to have the split in one go. But I see the point that a simple LOAD cannot provide this.

Re: Splitting a table during load?

Fri, 22 May 2015 15:36:47 GMT

Hi Jonathan,

actually I am turning the data into a cross table further down the road to be able to do other things in the reporting itself. It's what I do (have to do) with the data before I can convert it to a cross table, that's bothering me and makes me wonder if a cross table what help at that point. But it's worth some additional thinking. I'll keep you updated, if it helped.

Re: Splitting a table during load?

Fri, 22 May 2015 15:39:18 GMT

Hi,

as I replied to Jonathan further down - I will investigate into changing the order of cations in my script and see if turning the table into a cross table first, could help me.