topic Simple Conditional Load in QlikView

Simple Conditional Load

Tue, 09 Nov 2010 05:18:33 GMT

Hi,

I am new to QlikView but am loving it so far! I have a question about a datafile that I am loading - the file contains product sales data. What I discovered in the file is that it contains both sales and returns in the same file which is skewing my statistics.

The file has two fields I am interested in using for the conditional load - the first is TransactionID (unique for all purchases) and field called SalesCount (which has a value of 1 or -1 depending upon if the transaction was a sale (1) or a return (-1)).

I would like to eliminate the original sale (e.g., TransactionID=1234 and SalesCount=1) and the associated return (TransactionID=1234 and SalesCount=-1) of the items upon loading the data. Any ideas how I could do something like this?

Thank you in advance for any and all help!

Rick

Simple Conditional Load

Tue, 09 Nov 2010 05:37:01 GMT


Load TransactionID,
SalesCount*(-1) asSalesCount
From yourtable;

Simple Conditional Load

tresB — Tue, 09 Nov 2010 05:58:18 GMT

Hi,

its not clear in which condition you would like to eliminate the Original sales and associated return. Might be, If it is having a return ( SalesCount= - 1)...then only you don't want to take that sale and return into count. Is it so? If so then you can go with.....

LOAD *

FROM yourtable WHERE SalesCount<> -1;

Regards,

tresesco

Simple Conditional Load

Tue, 09 Nov 2010 06:44:11 GMT

Not sure what your data source is but in SQL Server you can have this load script:

ODBC CONNECT TO <YOURDATASOURCE> (XUserId is <YOURUSERNAME>, XPassword is <YOURPASSWORD>);
SQL SELECT <Field1>,
<Field2>,
<Field3>,
<Field4>,
TransactionID,
SalesCount
FROM <Transaction Table> A where A.SalesCount = 1 and A.TransactionID not in (select B.obi_shipmentid from <Transaction Table> B where B.TransactionID = A.B.TransactionID and B.SalesCount = -1);

Though, depending your business rule this may or may not work. Rule such as

1. multiple returns tied to single transaction

2. Partial returns (meaning the value is not equal to the actual transaction)

etc.

I hope this helps.

Simple Conditional Load

jonathandienst — Tue, 09 Nov 2010 06:52:08 GMT

Rick

If you want to eliminate the returns at source, then pinongs has the correct approach, but I think the FROM clause is not quite right. Suggested alternative:

SQL SELECT
...

FROM <Transaction Table> A where A.SalesCount = 1 and A.TransactionID not in
(select B.TransactionID from <Transaction Table> B where B.SalesCount = -1);

But do you really want to eliminate them at source? You might want that info in your model (ie returns analysis).

Jonathan

Simple Conditional Load

Tue, 09 Nov 2010 20:47:37 GMT

Hi All,

Thank you for all of the suggestions. The data was provided to us in a CSV file which I am loading into the model. For this particular model I would like to eliminate all sales and all returns that have the same TransactionID. This would be in two different lines of the data. I would just like to analyze the actual sales that people have not returned.

Does this provide the information that you need?

Thanks,

Rick

Simple Conditional Load

Miguel_Angel_Baeyens — Tue, 09 Nov 2010 22:26:09 GMT

Hello Rick,

Something like the following might work, although not likely the most beautiful way:

ReturnsMap:MAPPING LOAD TransactionID, SalesCountFROM File.csvWHERE SalesCount = -1; SalesNotReturned:LOAD TransactionID, SalesCountFROM File.csvWHERE SalesCount = 1 AND ApplyMap('ReturnsMap', TransactionID, '$') = '$';

It loads a mapping table in the first case only with those TransactionIDs that have Sales = -1. Then, this map is used to not to load those TransactionIDs that have SalesCount = 1 and are not in the mapping table (the '$' is the default value for those values for the field passed that are not in the mapping table, you can change that to any other symbol that's not present in yoru data).

Hope that's what you are looking for.

Simple Conditional Load

boorgura — Tue, 09 Nov 2010 22:28:39 GMT

You can achieve that using 2 steps.

First load the return transactions only.

Returns:

LOAD

field1, field2, field3, TrasactionID as returnID

from file.csv where <condition to get returns>

Then load the sales only and include the condition of "where not exists."

Sales_no_returns:

LOAD

field1, field2, field3, TrasactionID

from file.csv where <condition for sales only> and NOT EXISTS(returnID, TrasactionID);

Simple Conditional Load

boorgura — Tue, 09 Nov 2010 22:29:18 GMT

Of course you will have to drop the first table at the end.