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.

[Clever_Anjos]

Usually in QlikView less code means faster and less error-prone

[#3]

Thank you all for your responses, why do you think Am having this issue? I am trying Nicole’s suggestion but my problem is that I still need to load more information into this table and if I don’t do a Resident table I am unable to complete my final data table but from the moment I load everything into a new table then I lose records (usually credits) and I don’t understand why it is happening.

Thanks,

Hugo

[Nicole-Smith]

What else do you need to include?  Maybe it's possible to do without the resident load.

It's pretty much impossible for us to tell why your resident load isn't working without the actual DB and .qvw in front of us.

[maxgro]

you can post your qvw and qvd  filtering the select ( 106948 and 105169 and few other), so we'll be able to try, if you want.........

Main:

SELECT invoice, trntype, invdate, ccode, total FROM  invoices;  // add where ........, just some invoice

Concatenate

SELECT invoice, trntype, invdate, ccode, total FROM  credits;   // as above

store Main into Main.qvd (qvd);

exit script;

[mgavidia]

Hugo,

Once the data is in Qlikview, how can do you tell which ones are the invoices and which ones are the credits?

I tried to replicate the problem with the information you provided. I created 12 records for invoices and used the same data set for credits. I used the same code you have to load them in Qlikview.

From table Viewer, I can see that there are 24 records; however, a Table box will show only 12 because all fields are the same.There is no way to tell invoices from credits.

How do you know for sure that the records are missing?

Please check the number of records from Table Viewer and verify that they are the same.

As everyone else suggests, some sample data will be useful.

Can you dump a few records in Excel and upload a test file?

[#7]

Thank you Nicole,

I will upload some records in excel and provide code I am using, perhaps I am doing it all wrong to begin with.

I just wanted to say this is my first time posting something on the Qlik Community and I am surprised to the response so far. Thank you so much to all for your input. I will download the data and post in a few hours.

Thanks,

Hugo

[#8]

Hi Miguel,

Once the data is on QlikView I have a field called trntype which shows ‘I’ for invoices and ‘C’ for Credits but they are on separate fields. I will follow up with some more specific samples.

Thanks,

Hugo

[#9]

Hi to all,

I decided to follow Nicole's advice and I re-run my code, now it looks like this:

*********************************************************************************************************************

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;

*******************************************************************************************************************

I still can see two transaction even though they have the same invoice number but one is credit and the other one invoice. Now that I have the 'Main' table created I need to add some concatenations in order to get some unique keys to join to another file or tables. How can I procced to add the following key:

Year &'|'& Month &'|'& '|'& currency AS Key_Exch_Rates, I tried to add it to the about code and it returned errors because the Year and Month don't exist and if I tried to add a table and load all then I go back to my original problem of losing records. Also I have another table 'Customer' that has a sales rep, if I want to create another unique key Year &'|'& Month &'|'& '|'& salesrep AS Key_demand I get an error because the salesrep field is from another table, how can I concatenate fileds from different tables? both tables Main and Customer have the customer account as common field and they show join or related on table viewer.

P.S. I tried to load some code sample to replicate this issue but if I copy the code to excel and reload it on qlikview for testing then it seems like this issue doesnt' happen. However I cannot be copying all code to excel to pull back to qlikview. all my data is on dbf format coming directly from fox pro tables.

[#10]

Hello to all,

I think I discovered my error, I will double check and update my post tomorrow.

[Anonymous]

Create key and source fields for each load statement to distinct and identify the source. like below

Main:

Load

invoice & '-' & trntype as Key,

invoice, trntype, invdate, ccode, 'Invoices' as Source, total FROM  invoices;

Concatenate

Load

invoice & '-' & trntype as Key

invoice, trntype, invdate, ccode, 'Credits' as Source, total FROM  credits;

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

Drop Table Main;