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: 
Not applicable

Splitting a table during load?

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

9 Replies
Gysbert_Wassenaar

Nope, one load statement can generate only one table.


talk is cheap, supply exceeds demand
petter
Partner - Champion III
Partner - Champion III

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

sunny_talwar

I have an example which you might find useful.

Please have a look and see if this helps.

Best,

Sunny

petter
Partner - Champion III
Partner - Champion III

‌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?

sunny_talwar

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;

JonnyPoole
Employee
Employee

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)

Not applicable
Author

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.

Not applicable
Author

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.

Not applicable
Author

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.