Expressions and Dates

[#1]

I'm writing an expression that adds two values from seperate tables together.  The main table has data for 14 months in it and the other table in the expression has data for 24 months.  I want the output of this expression to cover 24 months instead of 14 and I don't know how to do that.  Any assistance would be greatly appreciated.

My thoughts are to create a blank set of fields to load into the main table that covers the additional 10 months worth of time.  Is this recommended or is there an easier way to solve this problem?

My sincerest thanks to any and all who reply,

Scott

[Miguel_Angel_Baeyens]

Hi Scott,

Are both tables referring to the same fact (say for example, Invoices)? If the answer is yes, then concatenate both tables to make a bigger one alone:

Invoices:
LOAD *
FROM Invoices2010.qvd (qvd);
CONCATENATE LOAD *
FROM Invoices2011.qvd (qvd);

The example is pretty dummy but you get the idea.

If the answer is no, please post some sample data and what results do you expect.

Anyway, creating a master calendar is a good idea. There are lots of examples in the Community, and you can find a working example on how to get that, and how to use it with variables and set analysis in this application.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#3]

Oops.  Instead of blank fields above, I meant blank data or dummy data in already existing fields that will be over-written as time goes on or is referencing a dummy product that was created for this purpose and does not impact the rest of the data in a significant way.

Thanks again.

[#4]

I'm trying to put in sample data to get my idea across, but this table editor won't let me cut and paste from excel.  It throws everything into one cell.  I'll just attach the file instead.

[Miguel_Angel_Baeyens]

Hi Scott,

I've changed a bit your data in the excel. Check the attached application to see whether that is what you were looking for.

Hope that helps, anyway.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#6]

I'm using personal edition.  It wants to use one of my four "recovery" things.  I don't understand the ramifications of using them up, so I'll have to hold off for now until I am sure of what the consequences are.  Can you put your suggestion into words instead?  I'm very sorry about the trouble, but unfortunately I can only use the software I'm given by my IT department.

Many thanks,

Gr8Scott

[Miguel_Angel_Baeyens]

Scott,

I set each of your table in a different sheet of the excel file instead of having all data in one sheet alone to make it readable by QlikView.

Here's the script:

Months14:
LOAD Product, 
     Date, 
     Orders AS Qty
FROM

(biff, embedded labels, header is 1 lines, table is [14months$]);

Months24: // Change all field names to be equal to the table above and allow automatic concatenation
LOAD Product, 
     Date, 
     Forecast AS Qty
FROM

(biff, embedded labels, header is 1 lines, table is [24months$]); 

Now with a simple table box you will get as you expect.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#8 · Accepted solution]

Miguel. I owe you an apology.  I figured out what my problem was.  I was trying to select this using only a date field that was exclusive to the orders table.  That is why I couldn't select all the dates.  I'm very new to QlikView and I didn't realize that it's only going to let you select what is available on that one table because that is all you are asking it for.  Rookie mistake on my part.

Thank you very much for being so helpful to me.  I am sorry that I made you work so hard to help me this only to find that the problem was related to the field I was selecting.  A very dumb error on my part.