How to fill missing data from an SQL source

[#1]

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

[#2]

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

[alexandros17]

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

[#4]

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.

[alexandros17]

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

[maxgro · Accepted solution]

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

[#7]

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?

[rohit214]

yes just add term_id coloum in

Tmp:

noconcatenate

Load

Region_ID

Month

Term_ID

resident

Source;

[maxgro]

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;

[rohit214]

Hi

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;