topic Re: filter in load in QlikView

filter in load

Sat, 31 Jan 2015 10:12:46 GMT

i have a load statement like below and i want to load data just for 30 top records of fulldate field.how should i change my code? is it possible?

OLEDB CONNECT TO [Provider=MSOLAP.3;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Sales;Data Source=172.18.100.19;Location=172.18.100.19];

LOAD

"[Measures].[F Sr Sales Quantity]" as [F Sr Sales Quantity],

"[Measures].[F Sr Gross Sales Amount]" as [F Sr Gross Sales Amount],

"[Branch Dim].[b City].[b City].[MEMBER_CAPTION]" as [b City],

"[Branch Dim].[b Name].[b Name].[MEMBER_CAPTION]" as [b Name],

"[Date Dim].[d Day].[d Day].[MEMBER_CAPTION]" as [d Day],

"[Date Dim].[d Month].[d Month].[MEMBER_CAPTION]" as [d Month],

"[Date Dim].[d Year].[d Year].[MEMBER_CAPTION]" as [d Year],

"[Date Dim].[Full Date].[Full Date].[MEMBER_CAPTION]" as [Full Date];

SELECT

{

[Measures].[F Sr Sales Quantity],

[Measures].[F Sr Gross Sales Amount]

}

DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS , NON EMPTY

CrossJoin(

{[Branch Dim].[b City].Children},

{[Branch Dim].[b Name].Children},

{[Date Dim].[d Day].Children},

{[Date Dim].[d Month].Children},

{[Date Dim].[d Year].Children},

{[Date Dim].[Full Date].Children})

ON ROWS

FROM [D Sys Warehouse]

;

Re: filter in load

Anonymous — Sat, 31 Jan 2015 11:10:39 GMT

Hello,

I think you can insert, above LOAD, the instruction FIRST 30 that load just the first 30 records.

FIRST 30

LOAD

....

...

If these 30 records must have a precise order respect to a Field, you can use instruction ORDER BY Field.

Hope this helps!

Elena

Re: filter in load

Anonymous — Sat, 31 Jan 2015 17:36:19 GMT

Hi,

Use the keyword FIRST before LOAD.

Data:

FIRST 30 LOAD *

FROM TableName

Order by fieldname;//use order by if necessary.

Or use limit load in debug dialog

Regards

Neetha

Re: filter in load

Gabriel — Sat, 31 Jan 2015 22:16:41 GMT

Hi,

I think you can load first 30 (or any x number you want) from your 1st table, by simply using key word FIRST 10 or 30. EXAMPL

LOAD

FIRST 30

"[Measures].[F Sr Sales Quantity]" as [F Sr Sales Quantity],

"[Measures].[F Sr Gross Sales Amount]" as [F Sr Gross Sales Amount],

"[Branch Dim].[b City].[b City].[MEMBER_CAPTION]" as [b City],

"[Branch Dim].[b Name].[b Name].[MEMBER_CAPTION]" as [b Name],

"[Date Dim].[d Day].[d Day].[MEMBER_CAPTION]" as [d Day],

"[Date Dim].[d Month].[d Month].[MEMBER_CAPTION]" as [d Month],

"[Date Dim].[d Year].[d Year].[MEMBER_CAPTION]" as [d Year],

"[Date Dim].[Full Date].[Full Date].[MEMBER_CAPTION]" as [Full Date];

SELECT

{

[Measures].[F Sr Sales Quantity],

[Measures].[F Sr Gross Sales Amount]

}

DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS , NON EMPTY

CrossJoin(

{[Branch Dim].[b City].Children},

{[Branch Dim].[b Name].Children},

{[Date Dim].[d Day].Children},

{[Date Dim].[d Month].Children},

{[Date Dim].[d Year].Children},

{[Date Dim].[Full Date].Children})

ON ROWS

FROM [D Sys Warehouse]

;

However, if you want your final output to return data order in any particular way then you will have to hold your initial LOAD in a Temp Table

i.e

Initial_Load_Temp:

LOAD

"[Measures].[F Sr Sales Quantity]" as [F Sr Sales Quantity],

"[Measures].[F Sr Gross Sales Amount]" as [F Sr Gross Sales Amount],

"[Branch Dim].[b City].[b City].[MEMBER_CAPTION]" as [b City],

"[Branch Dim].[b Name].[b Name].[MEMBER_CAPTION]" as [b Name],

"[Date Dim].[d Day].[d Day].[MEMBER_CAPTION]" as [d Day],

"[Date Dim].[d Month].[d Month].[MEMBER_CAPTION]" as [d Month],

"[Date Dim].[d Year].[d Year].[MEMBER_CAPTION]" as [d Year],

"[Date Dim].[Full Date].[Full Date].[MEMBER_CAPTION]" as [Full Date];

SELECT

{

[Measures].[F Sr Sales Quantity],

[Measures].[F Sr Gross Sales Amount]

}

DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS , NON EMPTY

CrossJoin(

{[Branch Dim].[b City].Children},

{[Branch Dim].[b Name].Children},

{[Date Dim].[d Day].Children},

{[Date Dim].[d Month].Children},

{[Date Dim].[d Year].Children},

{[Date Dim].[Full Date].Children})

ON ROWS

FROM [D Sys Warehouse]

;

Then Final Output:

LOAD

FIRST 30

"[Measures].[F Sr Sales Quantity]" as [F Sr Sales Quantity],

"[Measures].[F Sr Gross Sales Amount]" as [F Sr Gross Sales Amount],

"[Branch Dim].[b City].[b City].[MEMBER_CAPTION]" as [b City],

"[Branch Dim].[b Name].[b Name].[MEMBER_CAPTION]" as [b Name],

"[Date Dim].[d Day].[d Day].[MEMBER_CAPTION]" as [d Day],

"[Date Dim].[d Month].[d Month].[MEMBER_CAPTION]" as [d Month],

"[Date Dim].[d Year].[d Year].[MEMBER_CAPTION]" as [d Year],

"[Date Dim].[Full Date].[Full Date].[MEMBER_CAPTION]" as [Full Date]

RESIDENT Initial_Load_Temp

ORDER BY "[Date Dim].[Full Date].[Full Date].[MEMBER_CAPTION]" DESC (or ASC)

;

DROP TABLE Initial_Load_Temp;

This will give you first 30 order by whatever field of your preference.

Hope this helps

Re: filter in load

Anonymous — Sun, 01 Feb 2015 06:49:20 GMT

Hi Mana,

Use the script like this

First 30 LOAD [Activity Type],

Address,

[Billing Status],

[Blood Group],

From

.....

It will load the first 30 records only

Re: filter in load

sujeetsingh — Sun, 01 Feb 2015 07:33:31 GMT

Go with First keyword if Qlik or short by the value and generate record number as Recno() and in load write where RecNo()<=30