topic Re: data reduction help in QlikView

data reduction help

qlikmpate0 — Thu, 09 Jul 2015 09:52:10 GMT

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.

Re: data reduction help

avinashelite — Thu, 09 Jul 2015 10:03:33 GMT

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

Re: data reduction help

avinashelite — Thu, 09 Jul 2015 10:07:31 GMT

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;

Re: data reduction help

qlikmpate0 — Thu, 09 Jul 2015 10:10:04 GMT

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.

Re: data reduction help

avinashelite — Thu, 09 Jul 2015 10:24:30 GMT

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

Re: data reduction help

qlikmpate0 — Thu, 09 Jul 2015 10:37:33 GMT

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'

Re: data reduction help

avinashelite — Thu, 09 Jul 2015 10:42:46 GMT

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

Re: data reduction help

qlikmpate0 — Thu, 09 Jul 2015 10:47:39 GMT

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*';

Re: data reduction help

avinashelite — Thu, 09 Jul 2015 11:22:47 GMT

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

Re: data reduction help

qlikmpate0 — Thu, 09 Jul 2015 12:26:53 GMT

I have added the script to the original post.

Re: data reduction help

qlikmpate0 — Mon, 13 Jul 2015 09:35:34 GMT

Hi Avinash,

have you had a chance to look at my script?

regards

Re: data reduction help

avinashelite — Mon, 13 Jul 2015 09:59:57 GMT

Hi Mina,

Sorry for the delayed response , can you please share it an text format ?

As per my analysis don't use the like clause in the where condition , instead of that specific the exact department name

Re: data reduction help

qlikmpate0 — Mon, 13 Jul 2015 10:10:29 GMT

The attachment is in text format

Re: data reduction help

avinashelite — Mon, 13 Jul 2015 10:24:42 GMT

its not in text format ...its QVS

Re: data reduction help

qlikmpate0 — Mon, 13 Jul 2015 10:30:08 GMT

attached as *.txt file as per your request.

Re: data reduction help

vardhancse — Mon, 13 Jul 2015 10:32:25 GMT

Hi,

If we want only relevant records from the previous table use left join instead of inner join.

Mostly in your script there are inner joins

Re: data reduction help

qlikmpate0 — Mon, 13 Jul 2015 10:34:57 GMT

I have tried it with left joins and still getting the same results.

Re: data reduction help

vardhancse — Mon, 13 Jul 2015 10:41:05 GMT

we should do left join (fact table), then automatically we can get relevant records from all other tables.

Or else use

using where condition in the script level.

Re: data reduction help

qlikmpate0 — Mon, 13 Jul 2015 10:51:27 GMT

That was my next step, I will let you know how i get on. I already have the where clauses but I was thinking of creating a fact table and see where and what results I end up with.

Re: data reduction help

avinashelite — Mon, 13 Jul 2015 11:11:34 GMT

Hi Mina,

Your tables loading order should in the below order , please maintain the same order as below and change your script accordingly .

GLCodeCombinations:

Load *

from table;

left keep(GLCodeCombinations)

PO_Req_Distributions_All:

load

from

table;

Left(PO_Req_Distributions_All)

PO_REQ_Lines:

Load

from

table;

left keep(PO_REQ_Lines)

PO_Line_Locations:

Load *

from

table;

left keep(PO_REQ_Lines)

PO_REQ_Headers:

load

from

table;

left Keep (PO_Req_Distributions_All)

PO_Distributions_All:

load

from

table;

left keep(PO_Distributions_All)

PO_Lines:

load

from

tables;

left keep(PO_Lines)

PO_Headers:

load

from

tables;

left keep(PO_Headers)

POVendors:

load

from

tables;