How to create a sample doc ?

[paulyeo11]

Hi All

I have below script , after filter by brand = HMS , the file size still big , may i know how to further reduce the file size by only random select 5% of the data ?

Binary "C:\Users\Paul Yeo\Dropbox\0_Q_Development\0 Q_QVD\000_QVD_v2065.qvw";  // 1 yr

//C:\Users\Paul Yeo\Dropbox\0_Q_Development\0 Q_QVD

//Binary "c:\users\paul yeo\dropbox\0_q_development\0 qv snp development\0 q_qvd\000_qvd_v895.qvw";  // 7 yr

//Binary "c:\users\paul yeo\dropbox\0_q_development\0 qv snp development\0 q_qvd\000_qvd_v882.qvw";  // 2 yr

// local

//Binary "c:\users\paul yeo\dropbox\5 qv_final\0 qv production\000_qvd_v11.qvw";

NewFilteredTable:

NOCONCATENATE

LOAD *

RESIDENT sales_table WHERE BRAND_='HMS';

DROP TABLES sales_table;

/* below cannot reduce the file size also.

NewFilteredTable:

NOCONCATENATE

LOAD *

RESIDENT MasterCalendar WHERE year>'2013';

DROP TABLES MasterCalendar ;

*/

[tamilarasu]

Hi Paul,

In your script, both the tables are stored as  "NewFilteredTable". Obviously, this will create synchronization key. Try changing the table name.

[paulyeo11]

My issue are how to reduce the file size. If I change the table name file size will drop ?

Sent from my iPhone

[tamilarasu]

Not sure but try to avoid synthetic keys. Maybe someone might helps you.  You can check the link.

Synthetic Keys

[paulyeo11]

Hi sir

Sync key is no harm as it with me for last 6 yr

Paul

Sent from my iPhone

[MarcoWedel]

You could use the "sample" prefix to load statements to reduce the table content to some random samples.

e.g.

Sample 0.05
LOAD something
FROM somewhere

would load only 5% of this table.

hope this helps

regards

Marco

[paulyeo11]

Hi Marco

I have script like this below , can you share with me it is possible to add the script like you propose to me to get 5% sample data ?

sales:

LOAD left(FileBaseName(), 4) AS TDS,

'TDSPG' as SOURCE,

year(today()) - year(@50:60T) + 1                                         as Year_n,

(year(today()) - year(@50:60T)) * 12 + month(today()) - month(@50:60T) + 1 as Month_n,

@1:10T as [cust_id],

year([@50:60T]) as [year1],

Date(Monthstart(@50:60T), 'MMM-YYYY') as YearMonth_S,

Date(Monthstart(@344:354T), 'DD-MMM-YYYY') as YearMonthDay1

FROM Q_DOD_2015.txt (ansi, fix, no labels, header is 0, record is line);

[jagan · Accepted solution]

Hi Paul,

Instead of changing the script, you can select whatever the filters you want in the Dashboard and then follow the below steps to reduce the document size

File Menu-> Reduce -> Keep Possible Data

OR just use Sample keyword like below

sales:

Sample 0.05

LOAD left(FileBaseName(), 4) AS TDS,

'TDSPG' as SOURCE,

year(today()) - year(@50:60T) + 1                                         as Year_n,

(year(today()) - year(@50:60T)) * 12 + month(today()) - month(@50:60T) + 1 as Month_n,

@1:10T as [cust_id],

year([@50:60T]) as [year1],

Date(Monthstart(@50:60T), 'MMM-YYYY') as YearMonth_S,

Date(Monthstart(@344:354T), 'DD-MMM-YYYY') as YearMonthDay1

FROM Q_DOD_2015.txt (ansi, fix, no labels, header is 0, record is line);

Hope this helps you.

Regards,

Jagan.

[paulyeo11]

Hi Jagan

Great this is some thing new I just found out , thank you for sharing.

Sent from my iPhone

[jagan]

Hi Paul,

If you got the Answer close this thread by giving Correct and Helpful Answers to the posts.

Regards,

jagan.