data reduction help

[qlikmpate0]

Hi i'm try to retrieve all the purchase orders and requisiton for a department, I have restricted the first load which is my gl_code combination to only retrieve for the department I'm interested in and then retrieving the purchase orders for that department, but i'm getting all departments . I have attached the table joins as the data is too sensitive.

Please can anyone help?

Message was edited by: Mina Patel My Script is attached now.

[avinashelite]

Hi Mina,

Its hard  to identify on which table your imposing the condition ? and how your performing the join .

Can you please share the table names and join condition with script that would help

[avinashelite]

if your performing a normal join between the reduced table(you have imposed the condition ) and purchase table , try like

master table:

load *

table

where dept='a';

left keep

purchase table :

load *

from table1;

[qlikmpate0]

I have my restrictions on my GLCODECOMBINATIONS table  load *

where GLCodeCombinations.SEGMENT4 = 'Sales department'

The I have used the code combination_id to join to the PO_Distributions_all table.

and the POHEADERS and POLINES table are joined to the distribution, as are the requisition tables.

[avinashelite]

try like this:

GLCODECOMBINATIONS :

Load *;

from

GLCODECOMBINATIONS _table

where GLCodeCombinations.SEGMENT4 = 'Sales department';

left keep(GLCODECOMBINATIONS )

PO_Req_Distributions_all:

load * ;

from

PO_Req_Distributions_all_table;

left Keep(PO_Req_Distributions_all)

PO_Distributions_all:

load * ;

from

PO_Distributions_all_table;

same with the other two

or even you can use the exist function to achieve this

[qlikmpate0]

Tried but still not working. What I'm trying to do is this sql statement:-

SELECT head.SEGMENT1 AS "PO Num",

       line.LINE_NUM,

       vend.VENDOR_NAME,

       distr.QUANTITY_ORDERED,

       line.item_description,

       cat.segment1,

       distr.QUANTITY_DELIVERED,

       distr.QUANTITY_BILLED,

       distr.QUANTITY_CANCELLED,

       distr.rate,

       loc.CLOSED_CODE,

       LOC.NEED_BY_DATE,

       LOC.CLOSED_BY,

       LOC.CLOSED_DATE,

       line.UNIT_PRICE,

       gcc.SEGMENT1 AS "Entity",

       gcc.SEGMENT2 AS "MGMT",

       gcc.SEGMENT3 AS "Brand",

       gcc.SEGMENT4 AS "Department",

       gcc.SEGMENT5 AS "Account",

       gcc.SEGMENT6 AS "Proj Code"

  FROM po_distributions_all distr,

       po_lines_all line,

       po_headers_all head,

       gl_code_combinations gcc,

       mtl_categories_b cat,

       po_vendors vend,

       po_line_locations_all loc

WHERE     distr.PO_LINE_ID = line.PO_LINE_ID

       AND distr.PO_HEADER_ID = line.PO_HEADER_ID

       AND line.PO_HEADER_ID = head.PO_HEADER_ID

       AND distr.PO_HEADER_ID = head.PO_HEADER_ID

       AND distr.CODE_COMBINATION_ID = gcc.CODE_COMBINATION_ID

       AND distr.LINE_LOCATION_ID = loc.LINE_LOCATION_ID

       AND head.VENDOR_ID = vend.VENDOR_ID

       AND line.category_id = cat.category_id

       AND cat.structure_id = 201

       AND cat.segment1 LIKE 'DEPARTMENT'

[avinashelite]

your trying to reduce this in the SQL script .....OK then you cant specific the department name directly in the SQL condition itself rite ....like

AND cat.segment1 ='DEPARTMENT_NAME'

since your using the Like statement it fetching all the records with the value like department 

[qlikmpate0]

yes and no my qlikview script looks like the following, apologies, I'm a nvoice programmer

GLCodeCombinations:

Load

  *,

  GLCodeCombinations.SEGMENT2  & ' - '& Responsibility as [Management Resp];

LOAD GLCodeCombinations.CODE_COMBINATION_ID as [Join Code Combination ID],

  GLCodeCombinations.CODE_COMBINATION_ID,

   GLCodeCombinations.SEGMENT1,

     ApplyMap('LegalEntityMapping',GLCodeCombinations.SEGMENT1) as LegalEntity,

     GLCodeCombinations.SEGMENT2,

     ApplyMap('ResponsibilityMapping2',GLCodeCombinations.SEGMENT2) as Responsibility,

    // ApplyMap('ExpenseMapping',ApplyMap('ResponsibilityMapping',GLCodeCombinations.SEGMENT2)) as Responsibility,

     GLCodeCombinations.SEGMENT3,

     ApplyMap('BrandMapping',GLCodeCombinations.SEGMENT3) as Brand,

     //ApplyMap('MyMap',GLCodeCombinations.SEGMENT3),ApplyMap('BrandMapping',GLCodeCombinations.SEGMENT3) as Brand,

     GLCodeCombinations.SEGMENT4,

     ApplyMap('DepartmentMapping',right(GLCodeCombinations.SEGMENT4,2)) as Department,

     right(GLCodeCombinations.SEGMENT4,2) & ' - ' & ApplyMap('DepartmentMapping',right(GLCodeCombinations.SEGMENT4,2)) as [Department Full],

     //GLCodeCombinations.SEGMENT5 as [Account],

     ApplyMap('AccountRefMap',GLCodeCombinations.SEGMENT5) as [Account],

     //GLCodeCombinations.SEGMENT5 as [Join to GLHeaders],

     //left(GLCodeCombinations.SEGMENT5,3) as [Account Group2],

     ApplyMap('AccountMapping',left(GLCodeCombinations.SEGMENT5,3)) as [Account Group],

     GLCodeCombinations.SEGMENT6 as [Project Code],

     GLCodeCombinations.DESCRIPTION

FROM

[..\..\Finance\$(vQVDPath)\GL_Code_Combinations_UAT.qvd]

(qvd)

where left(GLCodeCombinations.SEGMENT5,1) = '8' and GLCodeCombinations.SEGMENT2 <> 'T'

and GLCodeCombinations.SEGMENT4 ='00061'

and GLCodeCombinations.SEGMENT5 like '82*';

[avinashelite]

no problem Mina, share your entire script in a text file , I will build the script and send you

[qlikmpate0]

I have added the script to the original post.