topic Re: Records disappearing in QlikView

Records disappearing

Thu, 20 Feb 2014 18:38:33 GMT

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.

Re: Records disappearing

Anonymous — Thu, 20 Feb 2014 18:46:58 GMT

Hugo:

I think we need to see your QVW file & maybe your date too..

Thanks.

CB.

Re: Records disappearing

Thu, 20 Feb 2014 19:22:00 GMT

Hi Car,

Thank you for your reply, the source of my files is fox pro database tables..... but they would be big. How can I post a sample?

Thanks,

Hugo

Re: Records disappearing

Thu, 20 Feb 2014 19:43:45 GMT

why didn't you have SQL command with ur select statements?

Re: Records disappearing

Thu, 20 Feb 2014 19:49:00 GMT

Hello there, I have no idea why, this project was passed onto me and I just took the initial code and started analyzing. Do you think that command will have a huge impact in the results? Sorry I am just learning qlikview and I am not too familiar with it but I find it a very powerful tool.

Thanks,

Hugo

Re: Records disappearing

Anonymous — Thu, 20 Feb 2014 19:58:03 GMT

Can you try this.. if it does not work .. please send us the qvw with some sample data.

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:

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

Drop Table Main;

Re: Records disappearing

Nicole-Smith — Thu, 20 Feb 2014 20:06:19 GMT

Why not just do it this way and avoid the resident load altogether?

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;

Re: Records disappearing

Anonymous — Thu, 20 Feb 2014 20:13:21 GMT

I agree with Nicole ... that is the best way to go

Re: Records disappearing

Thu, 20 Feb 2014 20:26:48 GMT

Thank you Nicole,

I am trying your solution as we speak, so using Resident tables can cause problems?

Thanks,

Hugo

Re: Records disappearing

Nicole-Smith — Thu, 20 Feb 2014 20:29:59 GMT

They shouldn't. But there is no need to use a resident table here. I always try and go with the least amount of code, and the way I did it would be that.

Re: Records disappearing

Clever_Anjos — Thu, 20 Feb 2014 20:31:34 GMT

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

Re: Records disappearing

Thu, 20 Feb 2014 20:47:48 GMT

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

Re: Records disappearing

Nicole-Smith — Thu, 20 Feb 2014 21:01:24 GMT

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.

Re: Records disappearing

maxgro — Thu, 20 Feb 2014 21:10:11 GMT

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;

Re: Records disappearing

mgavidia — Thu, 20 Feb 2014 21:53:15 GMT

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?

Re: Records disappearing

Thu, 20 Feb 2014 21:58:37 GMT

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

Re: Records disappearing

Thu, 20 Feb 2014 21:59:49 GMT

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

Re: Re: Records disappearing

Fri, 21 Feb 2014 03:09:48 GMT

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.

Re: Re: Records disappearing

Fri, 21 Feb 2014 03:46:41 GMT

Hello to all,

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

Re: Records disappearing

Anonymous — Fri, 21 Feb 2014 04:09:05 GMT

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;