QlikView App Dev

[#1]

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...

[Miguel_Angel_Baeyens · Accepted solution]

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.

[#3]

Hi Miguel,

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

Best Regards,

Bill

[Miguel_Angel_Baeyens]

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

[#5]

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..

[Miguel_Angel_Baeyens]

Hi Bill,

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

Regards.

Miguel

[#7]

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) ?

[#8]

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

[#9]

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

[Miguel_Angel_Baeyens]

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