topic How to get a new table with only required rows from old table with huge data in QlikView

How to get a new table with only required rows from old table with huge data

Sarath123 — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I have a sheet of data with 17776 rows. But I need only rows which has the code
110,1170,1201,1202,1203,1204,1205,1206,1207,1208,1209,1210,1211,1212,1213,1230,1250,1270,1290,1301,131,130270,1306,1390,153,156,157,158,159,160,1541,1542,1543,1544,1545,1546,1530,1547,1550,15500140,1570,1590,15904001,2101,2102,2103,2104,2107,2109,2301,2302,2309,2602,2603,2604,2609,2801,2901,3101,31010160,32010160,3201,31010107,32010107,4101,4201,4202,4203,4204,4209,11700101,1547,1550,1590,15904001,3101,3201,15500140,15500240,15904001,15904002,15904003,15904001,50904002,50904003,31010140,31010160,31010107
Hardly these are 91 rows + nine quarter sales for each code so totally 819 rows.
Can we create a new table with only these above codes to reduce the time complexity during loading of data
Looking for a solution on this case!!

Re: How to get a new table with only required rows from old table with huge data

StarinieriG — Thu, 12 Sep 2019 13:09:27 GMT

Hi,

have you tried to add a where condition?

Re: How to get a new table with only required rows from old table with huge data

Sarath123 — Thu, 12 Sep 2019 13:39:21 GMT

load *

resident [Austria]

where match(code,'1201','1202','1203');
STORE * from Austria into [M:user:New_AUSTRIA.csv];

I tried like the above but the new sheet is showing all the codes

Re: How to get a new table with only required rows from old table with huge data

StarinieriG — Thu, 12 Sep 2019 13:43:06 GMT

Try to use

STORE Austria into [M:user:New_AUSTRIA.csv];

without "* from"

Re: How to get a new table with only required rows from old table with huge data

StarinieriG — Thu, 12 Sep 2019 13:46:27 GMT

If you do a simple resident *, you have to rename table and use noconcatenate, so you are sure that tables are not automatically concatenate

Re: How to get a new table with only required rows from old table with huge data

Sarath123 — Thu, 12 Sep 2019 14:23:37 GMT

I wrote the script with your answer But it is not working .Let me provide you the script

Austria:
LOAD
Code,
TU_Company_QTR,
TU_Total_QTR,

FROM
[C:\Users\Desktop\book4.xlsx]
(ooxml, embedded labels, table is Sheet1);

NoConcatenate
load *
resident [Austria]
where match(Code,'1201','1202','1203');
STORE Austria into [C:\Users\Desktop\New_AUSTRIA.csv];
EXIT SCRIPT

Can you check my script and let me know the correction
If I can write for 3 codes, I will try to write for 91 codes as well

Re: How to get a new table with only required rows from old table with huge data

StarinieriG — Thu, 12 Sep 2019 14:14:22 GMT

NoConcatenate

Austria:
LOAD
Code,
TU_Company_QTR,
TU_Total_QTR,

FROM
[C:\Users\Desktop\book4.xlsx]
(ooxml, embedded labels, table is Sheet1)

where match(Code,'1201','1202','1203');

STORE Austria into [C:\Users\Desktop\New_AUSTRIA.csv];

Re: How to get a new table with only required rows from old table with huge data

Sarath123 — Thu, 12 Sep 2019 14:33:03 GMT

It is working Now
Thank you so much for your help

Re: How to get a new table with only required rows from old table with huge data

Sarath123 — Sun, 15 Sep 2019 15:38:16 GMT

I have created new excel sheet with only required columns and I did my calculations which are needed to calculated the sales for both Company and total sales.
But I am getting multiple fileds on the table with the nrmal sum of sales and calculated sales
e.g;
code companysales Total sales

1530 5000 -200000 ----This is the sum of sales code(1530)-code(XXXXX)-code(XXXX)

1530 100000 400000 ----This is sum of sales with the code(1530)

But I need only the first row with calcualted values that should contain in the table .Any suggestion how can I get first row