Records disappearing

[#1]

Hello,

I am new to QlikView and I have a document that pulls data from 2 different tables (all of them using the same field names) so the concatenation of all the tables works great without any problems. However when I load the resident table some of my records dissappear. The only possible issue I know is that I have invoices and credits the have the exact same number but they have different days (I have unique numbers for invoices and unique numbers for credits, but some of those numbers repeat between the two).

My Script is as follow:

Main:

SELECT invoice, trntype, invdate, ccode, total FROM  invoices;

Concatenate

SELECT invoice, trntype, invdate, ccode, total FROM  credits;

***from this I have invoice 106948 (one for credit and one for invoices) and I also have invoice 105169 (again one for credits and one for invoices)

****However my problem begins with the next load:

NoConcatenate
Data:
Load *, Year(invdate)AS Year, Month(invdate) AS Month, Date(invdate,'DD MMM YYYY') AS Date Resident Main;

Drop Table Main;

***from this I lose both credits and only see invoices for 106948 and 105169

Can anybody help me figure out what am doing wrong? I have tried RowNo() without any luck. Any help is greatly appreciated.

[Nicole-Smith]

Using the code that you have above:

Main:

LOAD *, Year(invdate) AS Year, Month(invdate) AS Month, Date(invdate,'DD MMM YYYY') AS Date;

SELECT invoice, trntype, invdate, ccode, total FROM  invoices;

Concatenate (Main)

LOAD *, Year(invdate) AS Year, Month(invdate) AS Month, Date(invdate,'DD MMM YYYY') AS Date;

SELECT invoice, trntype, invdate, ccode, total FROM  credits;

currency and salesrep aren't fields in either of these tables, which is why it's throwing errors.  If they're from different tables, how do you know which invoices and credits are related to what currency and salesrep?  You're going about your keys all wrong...

[#3]

Hi Nicole,

Here is what I want to do:

I have successfully created my master table (thanks to your help) now I have 3 tables: Master, CUST and Budget (Master and CUSTH are related by a common field ccode) my budget table contains month, year and sales rep and I have make a concatenation on these fields as Key_Demand_Budget. I want to find a way to link the budget table to the master.

The Master table contains month and year, but the sales rep is on the CUSTH table (I want to avoid to right join the CUSTH table to the MASTER because it creates other problems for me) I would like to create a key that matches my budget table made of Month and Year (from Master Table) and Sales Rep (from CUSTH) and insert this new field only onto my MASTER table. Is this possible?

Thanks,

Hugo

[Nicole-Smith · Accepted solution]

Load your CUSTH table in first.

Then:

Main:

LOAD *, Year & '|' & Month & '|' lookup('salesrep','ccode',ccode,'CUSTH') as Key_Demand_Budget;

LOAD *, Year(invdate) AS Year, Month(invdate) AS Month, Date(invdate,'DD MMM YYYY') AS Date;

SELECT invoice, trntype, invdate, ccode, total FROM  invoices;

Concatenate (Main)

LOAD *, Year & '|' & Month & '|' lookup('salesrep','ccode',ccode,'CUSTH') as Key_Demand_Budget;

LOAD *, Year(invdate) AS Year, Month(invdate) AS Month, Date(invdate,'DD MMM YYYY') AS Date;

SELECT invoice, trntype, invdate, ccode, total FROM  credits;

[#5]

That’s brilliant Nicole, you are a life saver, I really appreciate your help. To the entire QlikView community I want to thank you all for your support and I will definitely need lots of your help in the near future.

Nicole, I been researching how to concatenate fields from different preloaded tables and never found not even a single threat that recommended the ‘lookup’ function. Perhaps I didn’t word it properly but from now on I will ask my questions directly and sooner and with proper samples when I am not able to find the solution myself.

Thanks,

Hugo

[Nicole-Smith]

I'm glad it works for you, Hugo.

Please mark correct and helpful answers so that others can find solutions to similar problems