topic Re: New values in dataset in QlikView

New values in dataset

Thu, 04 Oct 2012 09:45:39 GMT

Hello.

I want to make a very simple task in Qlik and almost broke my head. I'm asking for community help.

I want to show new IDs with comparison to previous month. User will choose a month and will see a table with new IDs in this month.

For example:

ID	Date	Sold	Price
4448	04.2012	10,00	990,00
4448	05.2012	20,00	990,00
4499	04.2012	1,00	1 890,00
4544	04.2012	2,00	2 090,00
4595	04.2012	3,00	2 590,00
4624	05.2012	0,00	3 190,00
5096	05.2012	0,00	2 390,00

For 05.2012 user will see next new IDs and additional info about it:

4624

5096

What is the simpliest way to do it in QlikView?

WBR,
Dmitry.

Re: New values in dataset

Tue, 09 Oct 2012 14:13:52 GMT

Maybe somebody can point me to the similar discussions or solutions?

WBR,

Dmitry.

Re: New values in dataset

Gysbert_Wassenaar — Tue, 09 Oct 2012 14:31:24 GMT

For the new ID's the amount sold is 0? If so you can create a table that shows only the ID's where sold = 0. Then if a user selects a date in the listbox then you see only the new ID's for that date. Is that what you need?

Use a calculated dimension for the ID's: if(Sold = 0, ID) and a listbox for selection the date.

Re: New values in dataset

Wed, 10 Oct 2012 22:45:03 GMT

Unfortunately no. It's just an example, the real amount of data is huge and more complicated.

It's seems, that such simple calculation is not easy even for a famous Qlikview, it's a great pity...

Re: New values in dataset

Gysbert_Wassenaar — Thu, 11 Oct 2012 08:34:47 GMT

Yes, it's a shame qlikview can't read minds.

Could you explain what should happen when? How do we know which ID's are new?

Re: New values in dataset

Thu, 11 Oct 2012 17:10:20 GMT

Sorry, maybe I can't explain what I want to get correctly, my English is not perfect. I thought, that my example will clarify what I want to get.

Let me try again.

I've changed the example (removed 0 from sold):

ID	Date	Sold	Price
4448	04.2012	10,00	990,00
4448	05.2012	20,00	990,00
4499	04.2012	1,00	1 890,00
4544	04.2012	2,00	2 090,00
4595	04.2012	3,00	2 590,00
4624	05.2012	56,00	3 190,00
5096	05.2012	5,00	2 390,00

This data is the small part of real data (for 5 years). Imagine, that this is a full set of data. For this table I want to get the new IDs in 05.2012, comparing to the previous month 04.2012.

There is no special attribute in data for new IDs, only ID and date, so I need to compare all IDs in 04.12 with all IDs in 05.12 to find the difference. As you see its 4624 and
5096, this IDs appeared only in 05.2012, but I need an expression, which will work on any amount of data.

In report it will work in next way:

A user will choose any (but only one) month and will see the small table or list with new ID in this month comparing to previous month.

May be there is a way to show this new IDs only knowing IDs and dates?

WBR,

Dmitry.

Re: New values in dataset

Gysbert_Wassenaar — Thu, 11 Oct 2012 19:18:21 GMT

I think I can get the result you want. I added a flag that signals when an ID first appears in the record set. For this to work you do need to make sure the data is ordered by date If necessary you can add that clause to the load statement.

Can you check if the attached qvw does what you need?

Re: New values in dataset

johnw — Thu, 11 Oct 2012 19:33:08 GMT

I'm not sure what you mean by "comparing to previous month", but the attached example shows one possible interpretation and solution - an "AsOf" table. It uses years instead of months, but is otherwise very similar to your example.

Edit: Hmmm, no, I'm pretty sure I misinterpreted what you were after. Sorry.

Edit 2: gwassenar's solution of setting a flag during the load seems good.

Re: New values in dataset

Sun, 14 Oct 2012 17:32:27 GMT

Thank you, Gysbert.

It works perfect on example, but when I add this flag to my existing script - no luck.

First of all, I load data to Qlik from SQL DB using OLEDB and SQL Select script. As I understood, I can't use EXISTS function in SQL Select, only in LOAD statement, am I correct?

So, I've tried to make a LOAD RESIDENT script after SQL select to add a flag with EXISTS function, but it doesen't work, it shows only zeroes.

I found, that EXISTS function stop working, when you use it for LOAD RESIDENT script. You can try it, first to load data and then to add a NewFlag to a table:

Items:

load *

inline [...];

Newart:

load *,

if(Exists(ID),0,1) as NewFlag

Resident Items;

DROP Table Items;

Maybe I'm doing something wrong?

Re: New values in dataset

Gysbert_Wassenaar — Mon, 15 Oct 2012 08:00:06 GMT

Yes, Exists can't be used in the sql statement. But you can use the sql statement as a preceding load like this:

Items:

load *,

if(Exists(ID),0,1) as NewFlag;

SQL select ID, Date, Sold, Price

from MyTable;

Re: New values in dataset

Wed, 17 Oct 2012 07:34:05 GMT

Yes, it counts new IDs! Thank you again!