Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
raynac
Partner - Creator
Partner - Creator

String Data Field Empty in View

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.

1 Solution

Accepted Solutions
Not applicable

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?

View solution in original post

6 Replies
anbu1984
Master III
Master III

Can you post your script

raynac
Partner - Creator
Partner - Creator
Author

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.

Not applicable

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?

anbu1984
Master III
Master III

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;

raynac
Partner - Creator
Partner - Creator
Author

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.


raynac
Partner - Creator
Partner - Creator
Author

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