topic Re: How to fill missing data from an SQL source in QlikView

How to fill missing data from an SQL source

Thu, 13 Feb 2014 10:31:22 GMT

Hi,

I have a dashboard where I'm loading a data structure like this:

Region_ID	Value	Month	Product
ABC	348750	201402	Fixed Net
ABC	467556	201402	Mobile

So I have two rows in the database per Region and month, one for each product. The problem is now, that for some

Regions, there is only one Product available (let's say Mobile) and the other entry does not exist.

How can I create a dummy entry for those missing Product rows to always have two entries per Region and Month, e.g.:

Region_ID	Value	Month	Product
DEF	0 (Dummy entry)	201402	Fixed Net
DEF	45545	201402	Mobile

Thanks,

Jens

Re: How to fill missing data from an SQL source

Thu, 13 Feb 2014 10:39:00 GMT

Hi Jens

I don't know your goal but if it is to see you don't have any product according to date selection

I would say it is better to build your data model according your needs

best regards

Chris

Re: How to fill missing data from an SQL source

alexandros17 — Thu, 13 Feb 2014 10:41:37 GMT

When you load the table you may set up a control for the field, something like:

MyTable:

Select

....

if(myProd is null, 'N.A.', myProd) as myNewProd

...

Otherwise you may reload the table and set the same control with Qlik

Load myTable

...

if(isnull(myProd), ....

...

Hope it helps

Re: How to fill missing data from an SQL source

Thu, 13 Feb 2014 10:47:40 GMT

Thanks for your answer. I think I made my issue not clear enough or I misinterpret your suggestion.

My goal is to have two rows per Region and Month:

Region_ID	Value	Month	Product
ABC	348750	201402	Fixed Net
ABC	467556	201402	Mobile

but for some Regions, there is only one row:

Region_ID	Value	Month	Product
DEF	35475	201402	Fixed Net

So in these cases I would like to add a second row:

DEF 0 201402 Mobile

Thanks.

Re: How to fill missing data from an SQL source

alexandros17 — Thu, 13 Feb 2014 11:04:21 GMT

Left join your table with another table made with only the products:

NewTable:

Load

Region_ID

Value

Month

Product

from ...

Left Join

Load distinct Product as myProduct

from ...

In this way each row in the original table will be present for all the products you have.

At this point reload

NewTable and set a control (if) for existings values

Hope it helps

Re: How to fill missing data from an SQL source

maxgro — Thu, 13 Feb 2014 11:12:04 GMT

SCRIPT

Source:

LOAD * INLINE [

Region_ID, Value, Month, Product

ABC, 348750, 201402, Fixed Net

ABC, 467556, 201402, Mobile

DEF, 45545, 201402, Fixed Net

];

Tmp:

noconcatenate load distinct Region_ID, Month Resident Source;

join (Tmp)

LOAD * INLINE [

Product

Fixed Net

Mobile

];

Left join (Tmp) load * Resident Source;

DROP Table Source;

RESULT

Region_ID	Value	Month	Product
ABC	348750	201402	Fixed Net
ABC	467556	201402	Mobile
DEF	45545	201402	Fixed Net
DEF		201402	Mobile

Re: How to fill missing data from an SQL source

Thu, 13 Feb 2014 12:08:39 GMT

That is working fine, thanks.

Additional question:

For my example, I skipped some additional fields like Team_ID.

Is it possible to copy the Team_ID value for DEF from the existing Fixed Net row to the newly created Mobile row?

Re: How to fill missing data from an SQL source

rohit214 — Thu, 13 Feb 2014 12:13:15 GMT

yes just add term_id coloum in

Tmp:

noconcatenate

Load

Region_ID

Month

Term_ID

resident

Source;

Re: Re: How to fill missing data from an SQL source

maxgro — Thu, 13 Feb 2014 12:41:07 GMT

I added one row and one column to your data (bold) to check

look at the comments in the script

Source:

LOAD * INLINE [

Region_ID, Value, Month, Product, Team_ID

ABC, 348750, 201402, Fixed Net, 1

ABC, 467556, 201402, Mobile, 2

DEF, 45545, 201402, Fixed Net, 3

GHI, 12345, 201402, Mobile, 4

];

Tmp:

noconcatenate load distinct Region_ID, Month Resident Source;

join (Tmp)

LOAD * INLINE [

Product

Fixed Net

Mobile

];

Left join (Tmp) load * Resident Source;

// read Tmp and fill Team_ID when missing

// why order by ......?; because with peek I can peek the previous record value

// but I need to order the rows so when I find a null Team_ID I can peek the previuos Team_ID (not null)

// with same Region .Month

Table:

NoConcatenate load

Region_ID,

if(len(trim(Value))=0, 0, Value) as Value,

Month, Product, Team_ID,

if(len(trim(Team_ID))=0, peek(Team_ID), Team_ID) as New_Team_ID

Resident Tmp

order by Region_ID, Month, Value desc;

DROP Table Source;

DROP Table Tmp;

Re: How to fill missing data from an SQL source

rohit214 — Thu, 13 Feb 2014 12:43:35 GMT

Table1:

Load

Region_ID,

Value,

Month,

Product,

Term_ID

From

xyz.qvd;

join

Load

Region_ID,

Month,

Product as Product_Test,

Term_ID

If(Count(Region_ID)='1' and Product='Mobile','Fixed Net','Mobile') as Product

resident Table1 group by Region_ID,Month,Product,Term_ID;

Drop Field Product_Test;

Re: How to fill missing data from an SQL source

Thu, 13 Feb 2014 12:48:31 GMT

Thanks, but this is not necessary, I guess.

The Team_Id is always the same for a specific region

Region_ID, Value, Month, Product, Team_ID

ABC, 348750, 201402, Fixed Net, 1

ABC, 467556, 201402, Mobile, 1

DEF, 45545, 201402, Fixed Net, 2

So for the missing Mobile row of DEF, the team_id needs to be 2, too.