topic Re: How to create a sample doc ? in QlikView

How to create a sample doc ?

paulyeo11 — Thu, 22 Oct 2015 02:45:55 GMT

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 ;

Re: How to create a sample doc ?

tamilarasu — Thu, 22 Oct 2015 04:24:00 GMT

Hi Paul,

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

Re: How to create a sample doc ?

paulyeo11 — Thu, 22 Oct 2015 04:28:45 GMT

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

Sent from my iPhone

Re: How to create a sample doc ?

tamilarasu — Thu, 22 Oct 2015 04:43:14 GMT

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

Synthetic Keys

Re: How to create a sample doc ?

paulyeo11 — Thu, 22 Oct 2015 09:30:38 GMT

Hi sir

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

Paul

Sent from my iPhone

Re: How to create a sample doc ?

MarcoWedel — Thu, 22 Oct 2015 22:12:56 GMT

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

Re: How to create a sample doc ?

paulyeo11 — Fri, 23 Oct 2015 00:33:53 GMT

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

Re: How to create a sample doc ?

jagan — Fri, 23 Oct 2015 02:55:01 GMT

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.

Re: How to create a sample doc ?

paulyeo11 — Fri, 23 Oct 2015 03:03:55 GMT

Hi Jagan

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

Sent from my iPhone

Re: How to create a sample doc ?

jagan — Fri, 23 Oct 2015 03:11:58 GMT

Hi Paul,

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

Regards,

jagan.