topic Re: I hate looping issue of table structure in QlikView

I hate looping issue of table structure

Wed, 25 Apr 2012 07:09:19 GMT

What should I do? I really need to make this work...

I have tables,

RR_Header

rr_number

date

RR_Details

rr_number

item_code

received_qty

DR_Header

dr_number

date

DR_Details

dr_number

item_code,

delivered_qty

THIS IS ONLY SIMPLE TABLES....

RR_Header should link to RR_Details (rr_number)

DR_Header should link to DR_Details (dr_number)

RR_Details should link to RR_Details (item_code)

RR_Header should link to DR_Header (date)

So date I could have this report...

DATE	ITEM	DELIVERED	RECEIVED
12-12-12	A	10	15
11-11-11	B	5	8

but damn, i hate this thing... TABLE STRUCTURE LOOP... Whatever! It made my blood gets hot... Please help me about this...

I really need to meet the deadline...

I've attached the screenshot of the table structure...

Re: I hate looping issue of table structure

Miguel_Angel_Baeyens — Wed, 25 Apr 2012 07:46:50 GMT

Hi,

This is one of the most powerful QlikView features and the foundation of Associative Logic. If you don't understand this right, you better take some courses (there are good ones for free in the QlikView portal, Training).

Answering to your question, and in regards to the above: you need to rename the fields that are not the same values in different tables. For example, Customers may have a field named "Date" that is used to set the first purchase date, whilst Invoices may have another "Date" field that stores the date when the invoice was issued. Since they are not the same thing they must be renamed otherwise it will only lead to confusion and will return unexpected errors.

So your example should look like this:

Headers:
LOAD rr_number AS LinkHeaderDetails, date, 'RR' as Type INLINE [
rr_number, date
];
Details:
LOAD rr_number AS LinkHeaderDetails, item_code, received_qty INLINE [
rr_number, item_code, received_qty
];
 
DR_Header: // informative, not used
CONCATENATE (Headers) LOAD dr_number AS LinkHeaderDetails, date INLINE [
dr_number, date
];
 
DR_Details: // informative, not used
CONCATENATE (Details) LOAD dr_number AS LinkHeaderDetails, item_code, delivered_qty INLINE [
dr_number, item_code, delivered_qty
];

Hope that helps.

Miguel

EDIT: Check attached application. That's how a master calendar, fact table, dimension table and so on are built in their easiest and simplest way. You don't need to know datawarehouseing or datamining to get a QlikView application working, but you certainly do need to be familiar with these concepts. It's not only about avoiding loops, rather than creating working data models to return expected results in the best response time possible to help decision making process easier.

I hate looping issue of table structure

Wed, 25 Apr 2012 09:50:15 GMT

Hi Miguel,

Thank you so much for sharing this wonderful and helpful answered prayer. God bless.

Best Regards,

Bill

Re: I hate looping issue of table structure

Miguel_Angel_Baeyens — Wed, 25 Apr 2012 10:22:57 GMT

Hi Bill,

I'm glad to help. Bear in mind that, although QlikView is certainly easy to use, (or at least easier than oter tools) it all depends on what do you need to achieve. The QlikCommunity is the best resource of QlikView knowledge so far, with the largest number of users in the BI community. Most of them are real geniuses, and to mention a few, read John Witherspoon, Rob Wunderlich, Rakesh Mehta, Oleg Troyansky, Vlad Gutkovsky, Stephen Redmond, Ralf Becher, Jay Jakosky, Steve Dark, Gordon Savage, just to mention a few... There are more talented people of course, and is not unsual that I end up googling for "site:qlik.com iis configuration gutkovsky" for example because it's the fastest way to get a good answer to your problem, once you know which area of expertise belongs to whom.

But when it takes to data modelling, it's not just QlikView that, as any other software has its own "rules" (functions, syntax, you name it) rather than data modelling as you need to know to create a database or a datawarehouse (snowflake and star schemas, link tables, key fields, dimensions, fact tables...).

Fortunately, there is no "best answer" in most cases, and you can get to the same answer following different paths, not always being one of them better than the other, but there do are some good practices (usually based on the experience strengthened by your skills) that lead to create good applications both visual and performing.

Regards.

Miguel

I hate looping issue of table structure

Wed, 25 Apr 2012 10:42:39 GMT

Can you still help?

I still have one problem... this is continuation to above, there's another table.

SR_Header

sr_number

return date

SR_Details

sr_number

returned_qty

ref_no

This is to have this report

Date	Item	Date Return	Delivered	Return
12-12-12	A	12-15-12	10	15
11-11-11	B	11-20-12	5	8

DR_Details (dr_number) should link to SR_Details (ref_no)

I'm glad to be part of the forum..

Re: I hate looping issue of table structure

Miguel_Angel_Baeyens — Wed, 25 Apr 2012 10:58:50 GMT

Hi Bill,

One way to do it in the attached application. There are more for sure.

Regards.

Miguel

I hate looping issue of table structure

Wed, 25 Apr 2012 12:08:16 GMT

What if the dr_number of DR_Details should be link to SR_Details ref_no

Consider the following tables and data is related to above...

Example:

DR Detail

Item dr_number

A DR001

A DR002

B DR001

SR_Details

sr_number Item ref_no Returned Qty

SR01 A DR001 15

SR02 B DR001 8

SR_Header

sr_number date return

SR01 12-15-12

SR02 11-20-12

I need to have this report

1) DATE ITEM RETURN DATE DELIVERED RETURN

2) DATE ITEM SELL OUT SELL IN

*Sell out = Delivered - Return

*Sell In = Received

-I did what you have did w/ second qvw, I've notice the item has been link between DR Details and SR Details, could you make it DR Details (dr_number) and SR_Details (ref_no) ?

I hate looping issue of table structure

Wed, 25 Apr 2012 12:18:52 GMT

Im sorry I got wrong link...

DR_Details (dr_number) should link to SR_Header ( ref_no )

so this is the structure

SR_Header

sr_number ref_no

I hate looping issue of table structure

Wed, 25 Apr 2012 12:27:01 GMT

Hi Miguel Angel Baeyens,

Below are final structure. This is additional to first structure you have made in first Qlikview app.

DR Detail

Item dr_number

A DR001

A DR002

B DR001

SR_Details

sr_number Item Returned Qty

SR01 A 15

SR02 B 8

SR_Header

sr_number date return ref no

SR01 12-15-12 DR001

SR02 11-20-12 DR002

I need to have this report

1) DATE ITEM RETURN DATE DELIVERED RETURN

2) DATE ITEM SELL OUT SELL IN

*Sell out = Delivered - Return

*Sell In = Received

Re: I hate looping issue of table structure

Miguel_Angel_Baeyens — Wed, 25 Apr 2012 13:10:35 GMT

Bill,

The following should work. Note that these is not real data, so you will need to adapt or create more links between tables or modify the existing ones.

Hope that helps.

Miguel

I hate looping issue of table structure

Wed, 25 Apr 2012 13:58:06 GMT

Hi Mig,

I will apply the latest one okay? Will inform once work!

Regards,

Bill

Re: I hate looping issue of table structure

Wed, 25 Apr 2012 14:33:47 GMT

Hi Mig,

Have you check on this?

What if the dr_number of DR_Details should be link to SR_Details ref_no

Consider the following tables and data is related to above...

Example:

DR Detail

Item dr_number

A DR001

A DR002

B DR001

SR_Details

sr_number Item ref_no Returned Qty

SR01 A DR001 15

SR02 B DR001 8

SR_Header

sr_number date return

SR01 12-15-12

SR02 11-20-12

I need to have this report

1) DATE ITEM RETURN DATE DELIVERED RETURN

2) DATE ITEM SELL OUT SELL IN

*Sell out = Delivered - Return

*Sell In = Received

-I did what you have did w/ second qvw, I've notice the item has been link between DR Details and SR Details, could you make it DR Details (dr_number) and SR_Header (ref_no) ?

-Im sorry I forgot to tell you that the ref_no is not in SR_Details... it is in SR_Header...

The link of SR_Header to DR_Details is below

SR_Header

ref_no as dr_number

SR_Details

dr_number

something like that... Hehe

Re: I hate looping issue of table structure

Thu, 26 Apr 2012 05:34:00 GMT

Hi Mig,

Thanks for the help. I come up this structure. Thanks for the ideas. Got knowledge. Hehe

Best Regards,

Bill