Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

New values in dataset

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:

IDDateSoldPrice
4448                 04.201210,00990,00
4448                 05.201220,00990,00
4499                 04.20121,001 890,00
4544                 04.20122,002 090,00
4595                 04.20123,002 590,00
4624                 05.20120,003 190,00
5096                 05.20120,002 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.

1 Solution

Accepted Solutions

Re: New values in dataset

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?


talk is cheap, supply exceeds demand
10 Replies
Not applicable

Re: New values in dataset

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

WBR,

Dmitry.

Re: New values in dataset

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.


talk is cheap, supply exceeds demand
Not applicable

Re: New values in dataset

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

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?


talk is cheap, supply exceeds demand
Not applicable

Re: New values in dataset

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):

IDDateSoldPrice
4448                 04.201210,00990,00
4448                 05.201220,00990,00
4499                 04.20121,001 890,00
4544                 04.20122,002 090,00
4595                 04.20123,002 590,00
4624                 05.201256,003 190,00
5096                 05.20125,002 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

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?


talk is cheap, supply exceeds demand
MVP
MVP

Re: New values in dataset

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.

Not applicable

Re: New values in dataset

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

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;


talk is cheap, supply exceeds demand
Community Browser