topic Re: String Data Field Empty in View in QlikView

String Data Field Empty in View

raynac — Wed, 31 Dec 2014 15:19:21 GMT

I am having the strangest issue. I have a view that appears to be working in every way except one: I have a single field that is showing up blank, no matter if it should be or not.

The field has data in the database (at least in some records) and even though all records are loading in the script and showing in the tables/charts whether they use the field or not, the field itself is empty. It's just a basic string field, as far as I can tell. I have used upper() on it, as well as text(), in case there was something wonky about it and still nothing. I have gone into the Table Viewer and when I preview that table, everything looks great except that field is showing empty for every single record. So it's like it's not even loading from my SQL database.

Can anyone think of anything I am missing? I have never encountered this in all the views I have built and I am convinced it's something basic. Any insight would be deeply appreciated.

Re: String Data Field Empty in View

anbu1984 — Wed, 31 Dec 2014 16:28:44 GMT

Can you post your script

Re: String Data Field Empty in View

raynac — Wed, 31 Dec 2014 16:42:07 GMT

Hi there,

I cannot post the whole thing as it's several tabs long.

Here are the two tables with the discounts in them and the one field that's coming up blank (in the first table only) is the pos_discount_code:

pshist_i:

load

"pos_sequence" as "pos_sequence",

"pos_item_code" as "Item Code",

"pos_quantity_f6" as "Quantity",

"pos_price" as "Price",

"pos_sequence"&'-'&"suffix_no" as "Line Item",

"pos_discount_code",

"pos_discount_amount",

"pos_discount_percent",

"pos_extended_amount_bd8",

"pos_extended_amount_apd8",

"pos_extended_amount_fnl8"

;

SQL SELECT *

FROM $(TBLPRE)pshist_i i JOIN $(TBLPRE)pshist_h h

on i.pos_sequence = h.pos_sequence;

psdiscount:

LOAD

"pos_discount_code",

description as "POS Discount Desc";

SQL SELECT *

FROM "nw_src".dbo.psdiscount;

Even before I added the psdiscount table to obtain the discount description, the field was still showing as blank. The codes are all in the psdiscount table - and they are loading correctly, but not "matching" with any of the ones in pshist_i - so there are no codes or descriptions matching any of the items. I also tried it against another database on a server...no dice there either. It's utterly bizarre.

Re: String Data Field Empty in View

Wed, 31 Dec 2014 21:37:41 GMT

May be you can load the two tables pshist_i and pshist_h separately as two different tables and then see if pos_discount_code has values in it. This is just a sanity check to see whats going on. Also pos_discount_code is coming from which of those two tables?

Re: String Data Field Empty in View

anbu1984 — Thu, 01 Jan 2015 12:45:50 GMT

Is pos_discount_code present in both the tables pshist_i and pshist_h? If yes, then explicitly select this field from one of the tables which has values

SQL SELECT i.pos_discount_code,...

FROM $(TBLPRE)pshist_i i JOIN $(TBLPRE)pshist_h h

on i.pos_sequence = h.pos_sequence;

Re: String Data Field Empty in View

raynac — Mon, 05 Jan 2015 16:16:04 GMT

Hi there, Ajay!

Thanks so much for your help. I misread your message and said this: "I do load pshist_h separately - that's the header record. The discount code is applied to just the detail/item record, so it comes from pshist_i only. I was then using it to get the discount code description, but even before I brought that table in, it was malfunctioning."

Then I realized that I was joining the tables based on the pos_sequence, which I really didn't need to (it was left over from something else I was working on and never removed!). The minute I removed that clause, it worked like a charm. I then realized that was what you had meant all along! Thank you so much! I am certain I'd have missed that detail entirely.

Re: String Data Field Empty in View

raynac — Mon, 05 Jan 2015 16:17:26 GMT

Thank you so much for your help. Ajay pointed me in the right direction!