Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Alternative for concatenation to flag data?

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.

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

Re: Alternative for concatenation to flag data?

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

View solution in original post

13 Replies
Highlighted
Champion
Champion

Re: Alternative for concatenation to flag data?

provide screen shot of data or sample data if possible

Highlighted
Champion
Champion

Re: Alternative for concatenation to flag data?

most probably Cross table in below fashion

crosstable(data,Value,5)

Load

column1,

column2,

column3,

column4,

*

from path


Highlighted
MVP
MVP

Re: Alternative for concatenation to flag data?

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

Highlighted
Creator
Creator

Re: Alternative for concatenation to flag data?

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);

Highlighted
MVP
MVP

Re: Alternative for concatenation to flag data?

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

Highlighted
Creator
Creator

Re: Alternative for concatenation to flag data?

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!!!

Highlighted
Creator
Creator

Re: Alternative for concatenation to flag data?

Country IdMfr IDPeriodCompanyData1Data2Data3Data4
11002014Q1abc2344848743422432432934270
22002014Q2grt45685785547547433478793432
33002014Q3wer8347676565575343565687798
44002014Q4sdf429453425465465678978998796

there are about 10 miliion records.

Highlighted
MVP
MVP

Re: Alternative for concatenation to flag data?

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

Highlighted
Creator
Creator

Re: Alternative for concatenation to flag data?

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