Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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...
provide screen shot of data or sample data if possible
most probably Cross table in below fashion
crosstable(data,Value,5)
Load
column1,
column2,
column3,
column4,
*
from path
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..
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);
Can you also provide 5 to 6 lines of your data? or may be dummy data showing same column or field names?
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!!!
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.
I presume that Data1, Data2, Data3 and Data4 are the Sales_Date but where is the Flag/Type here?
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