Using MSQuery, IIF or Case

Hi Guys, my first question.

I'm using ms query to read Sage MMS Data. I'm reading SALES_TRANSACTIONS.TRANSACTION_TYPE which is returning 1, or 2, or 4, or 5. I want ms query to return either 6 or 7. if 1 then 7, if 2 then 6, if 4 then 6 else if 5 then 7.

Any pointers would be welcome.

Many thanks

Chris

My suggestion would be to return the {1, 2, 4, 5} in one column directly, and then use a second column next to it for processing like this:
A1:A100 = TRANSACTION_TYPE
B1 = IF( OR(A1 = 2, A1 = 4), 6, IF( OR(A1 = 1, A1 = 5), 7, "Exception"))
pull down

As a rule, External Data Connections just link to data and pull them back; do the processing/conversion on it once it's in excel.

Especially if you're doing something lossy like this that can't be reversed without reconnecting to the original data anyways.

Thanks for the quick reply Ben.

I'm coming to the conclusion that ms query, whilst being very clever is just for queries. If I want to do something with the data then the place to do it is in excel itself. I guess the clue is in the name ms query.

Thanks anyway.

Chris