topic Date problem in QlikView

Date problem

Thu, 12 Jan 2012 13:32:27 GMT

Hello,

I have 2 tables (Table VAT Entry en Table Intrastat) that have several thousands of lines of data from purchases etc...

Now I want to create a table that compares the purchases per month from the 2 tables. I have a TableA_Date field and a TableB_Date field. How do I combine these in 1 table so the both tables can be compared?

Regards,

Steven

Date problem

Thu, 12 Jan 2012 14:00:27 GMT

I guess its more complicated than it sounds... 31 views and no reply

Date problem

swuehl — Thu, 12 Jan 2012 14:03:16 GMT

There are probably some different approaches.

One simple would be to create a month field from the Date fields in both tables with same name, linking both tables.

Then create a table with month as dimension and two expressions summing the values you want to compare.

Date problem

Thu, 12 Jan 2012 14:04:20 GMT

Throw some data at us so we can see what you're looking to do. It doesn't sound hard, but at the same time...you never know till you see it. There's many ways to accomplish it. One way is to just join the two tables together based on date and compare the data that way. but I suspect there's a better way to do it.

Re: Date problem

Miguel_Angel_Baeyens — Thu, 12 Jan 2012 14:04:22 GMT

Hi Steven,

I would concatenate both tables renaming as needed so they share the most relevant field names (date, amount, quantity), then create a master calendar from these dates.

FactTable:

LOAD Date,

Amount,

Quantity,

Customer,

Country,

'VAT Entry' AS Fact_Type

FROM ... // file or database source for Table_VAT_Entry

CONCATENATE Table_VAT_Entry LOAD Date,

Amount,

Quantity,

Customer,

Country,

'Intrastat' AS Fact_Type

FROM .. // file or database source for Table Intrastat

If source files or database have different file names, you will need to rename them.

Hope that helps.

Miguel

Date problem

Thu, 12 Jan 2012 14:05:14 GMT

Could you give an example?

Re: Date problem

Thu, 12 Jan 2012 14:40:10 GMT

This is the load for the intrastat

Journal:
LOAD "Country_Region Code" AS "J_Country_Region Code",
    Amount AS "J_Amount",
    "Document No_" AS "IL_Shipment No_",
    "Item No_" AS "J_Item No_",
    "Transaction Specification" AS "J_Transaction Specification",
    "Date" AS "J_Date";
SQL SELECT "Country_Region Code",
    Amount,
    "Document No_",
    "Item No_",
    "Transaction Specification",
    "Date"
FROM FILE;

And a selection of the VATEntry:

VATEntry:
LOAD Amount AS "VE_Amount",
    Base AS "VE_Base",
    "Base Before Pmt_ Disc_" AS "VE_Base Before Pmt_ Disc_",
//key voor Vendor
    "Bill-to_Pay-to No_" AS V_No_,
    "Country_Region Code" AS "VE_Country_Region Code",
    if([Country_Region Code]='BE',0,1) AS VE_Country_Selection,
    "Document No_" AS "VE_Document No_",
    "Document Type" AS "VE_Document Type",
    "Enterprise No_" AS "VE_Enterprise No_",
    "EU 3-Party Trade" AS "VE_EU 3-Party Trade",
    "Gen_ Bus_ Posting Group" AS "VE_Gen_ Bus_ Posting Group",
    "Posting Date" AS "VE_Posting Date";
SQL SELECT Amount,
    Base,
    "Base Before Pmt_ Disc_",
    "Bill-to_Pay-to No_",
    "Country_Region Code",
    "Document No_",
    "Document Type",
    "Enterprise No_",
    "EU 3-Party Trade",
    "Gen_ Bus_ Posting Group",
    "Posting Date",
FROM FILE;

I want to compare sum(VE_Amount) and sum(J_Amount) for each month ([VE_Posting Date] and [J_Date]). If I simply create a field Month there is a loop due to other tables.