topic Re: Alternative for concatenation to flag data? in QlikView

Alternative for concatenation to flag data?

Anonymous — Tue, 14 Apr 2015 06:13:36 GMT

I have a csv file about 480 MB. It has 8 columns. column 5 to 8 are data columns which i concatenate one by one in order to flag the kind of data it has. This 4 tyimes concatenation is causing huge perf issue. Even I do the alternative of Crosstable, same problem. Please advice any other alternative.

Re: Alternative for concatenation to flag data?

SunilChauhan — Tue, 14 Apr 2015 06:21:36 GMT

provide screen shot of data or sample data if possible

Re: Alternative for concatenation to flag data?

SunilChauhan — Tue, 14 Apr 2015 06:23:07 GMT

most probably Cross table in below fashion

crosstable(data,Value,5)

Load

column1,

column2,

column3,

column4,

from path

Re: Alternative for concatenation to flag data?

MK_QSL — Tue, 14 Apr 2015 06:24:06 GMT

You can use CrossTable one by one and Concatenate them..

Better solution can be given only upon looking into your excel file or script which have written..

Re: Alternative for concatenation to flag data?

Anonymous — Tue, 14 Apr 2015 06:50:54 GMT

Using Crosstable and concatenating or simply concatenating, both have same impact on the size of the final table generated. I want to reduce the size of the table.

I want the four columns to become one column called 'Sales_Data' and then use flag to know the type.

Here is my script.

LOAD @1,

@2,

@3,

@4,

@5 as Sales_Data,

'LCD/TRD' as Type

FROM

[..\Source Data\IMS_Interface\QVDs\SalesFact.qvd]

(qvd);

LOAD @1,

@2,

@3,

@4,

@6 as Sales_Data,

'LCD/MNF' as Type

FROM

[..\Source Data\IMS_Interface\QVDs\SalesFact.qvd]

(qvd);

LOAD @1,

@2,

@3,

@4,

@7 as Sales_Data,

'USD' as Type

FROM

[..\Source Data\IMS_Interface\QVDs\SalesFact.qvd]

(qvd);

LOAD @1,

@2,

@3,

@4,

@8 as Sales_Data,

'SU' as Type

FROM

[..\Source Data\IMS_Interface\QVDs\SalesFact.qvd]

(qvd);

Re: Alternative for concatenation to flag data?

MK_QSL — Tue, 14 Apr 2015 06:53:01 GMT

Can you also provide 5 to 6 lines of your data? or may be dummy data showing same column or field names?

Re: Alternative for concatenation to flag data?

Anonymous — Tue, 14 Apr 2015 06:53:55 GMT

But I want the last four columns to become one column called 'Sales_Data' and then use flag to know the type of data. But this is increasing the size!!!

Re: Alternative for concatenation to flag data?

Anonymous — Tue, 14 Apr 2015 07:01:36 GMT

Country Id	Mfr ID	Period	Company	Data1	Data2	Data3	Data4
1	100	2014Q1	abc234	484874	34224	324329	34270
2	200	2014Q2	grt456	857855	4754743	347879	3432
3	300	2014Q3	wer834	76765	65575	3435656	87798
4	400	2014Q4	sdf429	45342	546546	56789789	98796

there are about 10 miliion records.

Re: Alternative for concatenation to flag data?

MK_QSL — Tue, 14 Apr 2015 07:14:02 GMT

I presume that Data1, Data2, Data3 and Data4 are the Sales_Date but where is the Flag/Type here?

Re: Alternative for concatenation to flag data?

Anonymous — Tue, 14 Apr 2015 07:17:32 GMT

Yes. You are correct. Those 4 columns are 4 types of Sales_Data. there is no flag/Tpye given. I need to introduce it.

Example:

Type:

Abs - for @5

Exc - for @6

Res - for @7

USD - for @8

Re: Alternative for concatenation to flag data?

MK_QSL — Tue, 14 Apr 2015 07:24:53 GMT

Temp:

Load [Country Id],[Mfr ID], Period, Company, Data1, Data2, Data3, Data4 From YourQVDName;

Final:
Load [Country Id],[Mfr ID], Period, Company, 'Abs' as Flag, Data1 as Value Resident Temp;

Load [Country Id],[Mfr ID], Period, Company, 'Exc' as Flag, Data2 as Value Resident Temp;

Load [Country Id],[Mfr ID], Period, Company, 'Res' as Flag, Data3 as Value Resident Temp;

Load [Country Id],[Mfr ID], Period, Company, 'USD' as Flag, Data4 as Value Resident Temp;

Drop Table Temp;

=============================================

First load full data from QVD rather than loading 4 different times. Now use Resident load which is much faster than Optimized Load... This will definitely reduce your load time...

Re: Alternative for concatenation to flag data?

Anonymous — Tue, 14 Apr 2015 08:00:01 GMT

Thank you. Yes it did reduce my load time! It would be helpful if you can also guide how to reduce the size of the table. As my primary concern is sizeas it increases my QVw size a lot and make the data modeling slower. Thank you.

Re: Alternative for concatenation to flag data?

MK_QSL — Tue, 14 Apr 2015 08:01:47 GMT

There is no way to reduce the size as you want to load all data and also want to transform the table with different flags...

Re: Alternative for concatenation to flag data?

Anonymous — Tue, 14 Apr 2015 08:15:45 GMT

Yeah.. okay.. Thanks a lot!!