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
columns for prod A
columnA > 0
columns for prod B
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
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?
I totally forgot this was Qlik Sense discussion. But the idea might still work. Let me paste the script here:
CrossTable(Measurement, Data, 3)
[Price 1] & '|' & [Quantity 1],
[Price 2] & '|' & [Quantity 2]
(ooxml, embedded labels, table is Sheet1);
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
DROP Table Table;
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:
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.
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.
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.