filter in load

[#1]

hi

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]

;

[Anonymous]

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

[Anonymous]

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

[Gabriel]

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

[Anonymous]

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

[sujeetsingh]

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