Solved: Records disappearing - Qlik Community

Report Inappropriate Content · ‎2014-02-20

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 · ‎2014-02-21

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;

View solution in original post

Anonymous · ‎2014-02-20

Hugo:

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

Thanks.

CB.

Report Inappropriate Content · ‎2014-02-20

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

Report Inappropriate Content · ‎2014-02-20

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

Report Inappropriate Content · ‎2014-02-20

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

Anonymous · ‎2014-02-20

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;

Nicole-Smith · ‎2014-02-20

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;

Anonymous · ‎2014-02-20

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

Report Inappropriate Content · ‎2014-02-20

Thank you Nicole,

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

Thanks,

Hugo

Nicole-Smith · ‎2014-02-20

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.