topic Re: How can link table with master calendar in QlikView

How can link table with master calendar

prma7799 — Mon, 11 Apr 2016 05:07:19 GMT

Hi All,

I have one data model created based on using link table i want to add one more extra table(table:ABC) in that so

How can I link that table .

I already link (table:ABC) to one of fact table but when i am selecting any master calendar field then values are getting wrong.

Please help

Re: How can link table with master calendar

Kushal_Chawda — Mon, 11 Apr 2016 05:33:50 GMT

How you are linking that ABC table? I mean on which field?

Re: How can link table with master calendar

prma7799 — Mon, 11 Apr 2016 05:39:22 GMT

On div_code...

Please see below

FACT_GL_Entry1:

LOAD [Entry No_] AS EntryNo,

Div_Code &'-'& num (month(([Posting Date]))) as TKye,

AUTONUMBER(/*[G_L Account No_] & '_' & */date([Posting Date],'DD/MM/YYYY')
& '_' & BusSeg_Code & '_' & AccLoc_Code & '_' & [Gen_ Bus_ Posting Group]
& '_' & [Gen_ Prod_ Posting Group] & '_' & [Location Code]
& '_' & [Document No_]) AS %GLKey,

     Div_Code,
Dept_Code,
[Document Type],
[G_L Account No_] AS GLAcc_Code,
      Amount AS GL_Amount,
//BusSeg_Code,
     //AccLoc_Code,
     Quantity AS GL_Qty,

date([Document Date],'DD/MM/YYYY') AS GL_DocDate,

[Source Code]

FROM D:\Production\Qvds\NAVQVDS\FACTQVGLE.qvd (qvd);

Left join (FACT_GL_Entry1)
Load GLAcc_Code,
GLAcc_name,
GL_Market_Seg,
GL_Buss_Seg,
GL_Buss_Seg1,
Type1,
TYPE,
[Note No],
[Note Name],
Subsch1,
Subsch2,
GLAcc_Code1,
MISFLAG
FROM
[..\Excels\PNO\GL Account Master_Customized_NEW.xlsx]
(ooxml, embedded labels, table is Sales);

join (FACT_GL_Entry1)
//DIM_Division_Region:
LOAD HOD,
[Business Segment],
Region as Div_Region,
[Country / Division] as Div_Name ,
[Division Code] as Div_Code
FROM
[..\Excels\PNO\Master File - Region Division 09.03.16.xls]
(biff, embedded labels, table is Final$);

join (FACT_GL_Entry1)
//DIM_Division_Master:

LOAD

Div_Code,
Div_Name

FROM
[..\Qvds\NAVQVDS\DIMQVDivision.qvd]
(qvd);

FACT_GL_Entry:
load * ,
IF(Div_Code>2010 AND Div_Code < 2999,'Regulated',
IF(Div_Code>3000 AND Div_Code < 3999,'Semi Regulated','Others')) AS GL_Market,
IF(Div_Code>2010 AND Div_Code < 3999,'Export',
IF(Div_Code=5020 ,'Export',
IF(Div_Code>3000 AND Div_Code < 3999,'Export','Domestic'))) AS Division_Class

resident FACT_GL_Entry1;

DROP Table FACT_GL_Entry1;

Targets:
LOAD //Div_Code &'-'& num (month(([Posting Date]))) as TKye,
       //[Business Segment],
     // Region,
      Div_Code ,
Country,
// [Posting Date] as DAte,
      Target
FROM
[..\Qvds\NAVQVDS\Target.qvd]
(qvd);

Re: How can link table with master calendar

Kushal_Chawda — Mon, 11 Apr 2016 06:02:17 GMT

which is the table in which you are facing issue while loading?

Re: How can link table with master calendar

prma7799 — Mon, 11 Apr 2016 06:11:34 GMT

am not facing any issue but in Targets: table when I select financial year, monthname or quarter then Targets are not shown right figure.

Thanks

Re: How can link table with master calendar

prma7799 — Mon, 11 Apr 2016 07:20:05 GMT

Anyone please look here..............

Re: How can link table with master calendar

tresB — Mon, 11 Apr 2016 07:26:10 GMT

I don't see a year or month field in your code. Could you post your sample qvw (with reduced/sample data)?

Re: How can link table with master calendar

tresB — Mon, 11 Apr 2016 10:16:15 GMT

Can't open your qvw; getting 'access denied'.

Re: How can link table with master calendar

prma7799 — Mon, 11 Apr 2016 10:41:34 GMT

tresesco‌

PFA

Re: How can link table with master calendar

tresB — Mon, 11 Apr 2016 11:03:26 GMT

Your Target value is from Div_Code - 2180 and there is no entry for this division in the transaction table.

Re: How can link table with master calendar

prma7799 — Mon, 11 Apr 2016 11:34:13 GMT

tresesco‌

as you see we have transaction on 2180 please check

My question is when I am selecting calendar field then I am getting monthwise targets ...

Please check this excel file on basis of this excel i have created Target table....

Please help.

Re: How can link table with master calendar

tresB — Mon, 11 Apr 2016 11:59:44 GMT

I meant there is no entry in the 'FACT_GL_Entry' table for 2180. Since this table is the link between, Target table and LinkTable, it could not find a date in master calendar table.

Re: How can link table with master calendar

prma7799 — Mon, 11 Apr 2016 12:17:48 GMT

tresesco Sir

without any entries how can it is sales against that code.

As you can see above image we have sale on that code and i have confirmed from IT team also .

There is any another way to get all targets when we select month or quarter ..... or front end ...

Thanks

Re: How can link table with master calendar

tresB — Mon, 11 Apr 2016 12:33:00 GMT

I guess, you are referring a different qvw which is not shared here. I see only one div code in that table.

Re: How can link table with master calendar

prma7799 — Mon, 11 Apr 2016 12:43:50 GMT

Yes ....

I just debug with 1 line and give to you but now am achieving the same ... but some div code is showing wrong targets.

Could you please tell me How can I optimize that qvw file which is send to you.....

I think its very complex and I am getting very difficulties to add new transaction table in that qvw ...

Hope you understand what i want to say .....

Your help will be helpful to me to understand how to handle this type of application and how we can add any new table in this....

Please guide....

Re: How can link table with master calendar

tresB — Mon, 11 Apr 2016 12:51:51 GMT

I would have been very happy, if I could help you with this specific case - but it would require a lot of time which I don't have now. I would suggest you to go through some very nice threads and blogs that discuss about the same and you would probably find them helpful.

Joins and Lookups

Re: Create link between two tables?..

Best Practices to Concatenate Multiple Fact Tables?

Re: How can link table with master calendar

tresB — Wed, 13 Apr 2016 05:45:43 GMT

This is a kind of Chasm Trap. You may refer : Fan traps and Chasm traps