How to add columns together based on a specific criteria (another field)

[#1]

I'm fairly new to Qlikview, and I have a question on how to add columns together for a rolling set of dates.

I have an app that is intended to track holes in our data. One thing I need to know, in order for this app to work, is on what date should I expect a particular data source. So, I will have a table called "Expected_Lag", that will basically look like this:

data_source_code     expected_lag

     AOL                              1    

     Apple                           1    

     Hulu                             7    

     NDN                            1

     XBox                            7

This means that I should expect an AOL data source 1 day after the current day, a Hulu source 7 days after the current day, and so on.

I've attempted to join this table to my regular data table, so that every data_source_code has a specific expected_lag. What I'd then like to do is have a dimension where I add my date (activity_date) to expected_lag, to get the Expected Delivery Date. So, if today is 9/6, the data_source_code AOL should have an Expected Delivery Date of 9/7. Right now I'm achieving this in the load script by having:

Date(activity_date+expected_lag) as ExpectedDelivery

However, the result is that I'm getting a table where every data_source_code has an ExpectedDelivery of +1 and +7 to the associated activity date. I would like for the expected_lag in the above expression to be distinct for each data_source_code, but I am unsure of how to achieve this. I know that I could just have the above as an expression in a table, but I want for the ExpectedDelivery date to be populated without having to make an activity_date selection.

I have attached my qvw for reference. Any help would be most appreciated. Thanks!

[#2 · Accepted solution]

You have not loaded data correctly:

Try somthing like below:

Jobs_Metadata:

LOAD //if(isnull(data_source_code),peek(data_source_code),data_source_code) as data_source_code,
data_source_code,
job_id,
customer_code,
activity_date,
activity_date_end,
partner_code,
country_code,
format,
product_type,
data_type,
records,
posted_quantity,
posted_revenue,
total_quantity,
total_revenue,
royalty_currency,
errors,
warnings,
created_date,
etl_processed_date,
last_validated
FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Left join(Jobs_MetadataJobs_Metadata)

//Expected_Lag:

LOAD data_source_code,
expected_lag
FROM
[test feeds table data_source_code expected lag table.xlsx]
(ooxml, embedded labels, table is Sheet1);

Expected_Date_Calculation:

Load *,

Date(activity_date+expected_lag) as ExpectedDelivery

Resident Jobs_MetadataJobs_Metadata;

Drop table Jobs_MetadataJobs_Metadata;

[#3]

Thanks Kirti for your reply. Unfortunately, this revision to the load script did not work. I am getting Table Not Found errors when running the load script.

Table not found

Left join(Jobs_MetadataJobs_Metadata)

LOAD data_source_code,

expected_lag

FROM

[test feeds table data_source_code expected lag table.xlsx]

(ooxml, embedded labels, table is Sheet1)

Table not found

Expected_Date_Calculation:

Load *,

Date(activity_date+expected_lag) as ExpectedDelivery

Resident Jobs_MetadataJobs_Metadata

[swuehl]

The table is named Jobs_Metadata, not Jobs_MetadataJobs_Metadata (looks like a copy & paste error), so whenever you see Jobs_MetadataJobs_Metadata, replace with Jobs_Metadata.

[#5]

Thanks! That did the trick.