Discussion Board for collaboration related to QlikView App Development.
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.
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
if your performing a normal join between the reduced table(you have imposed the condition ) and purchase table , try like
purchase table :
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.
try like this:
where GLCodeCombinations.SEGMENT4 = 'Sales department';
left keep(GLCODECOMBINATIONS )
load * ;
load * ;
same with the other two
or even you can use the exist function to achieve this
Tried but still not working. What I'm trying to do is this sql statement:-
SELECT head.SEGMENT1 AS "PO Num",
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,
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'
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
yes and no my qlikview script looks like the following, apologies, I'm a nvoice programmer
GLCodeCombinations.SEGMENT2 & ' - '& Responsibility as [Management Resp];
LOAD GLCodeCombinations.CODE_COMBINATION_ID as [Join Code Combination ID],
ApplyMap('LegalEntityMapping',GLCodeCombinations.SEGMENT1) as LegalEntity,
ApplyMap('ResponsibilityMapping2',GLCodeCombinations.SEGMENT2) as Responsibility,
// ApplyMap('ExpenseMapping',ApplyMap('ResponsibilityMapping',GLCodeCombinations.SEGMENT2)) as Responsibility,
ApplyMap('BrandMapping',GLCodeCombinations.SEGMENT3) as Brand,
//ApplyMap('MyMap',GLCodeCombinations.SEGMENT3),ApplyMap('BrandMapping',GLCodeCombinations.SEGMENT3) as Brand,
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],
where left(GLCodeCombinations.SEGMENT5,1) = '8' and GLCodeCombinations.SEGMENT2 <> 'T'
and GLCodeCombinations.SEGMENT4 ='00061'
and GLCodeCombinations.SEGMENT5 like '82*';
no problem Mina, share your entire script in a text file , I will build the script and send you
I have added the script to the original post.