Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

crystles
Contributor III

Apply value to every row in table

I have master list of customers. I then have a table that is linked to this table by a key field with a Fiscal Quarter and a Sales field.

The problem is, that the second table doesn't have a value for the Sales for each Quarter, for each customer. I need Sales to have a value and not be null in this table for each Quarter, but I'm not sure how to accomplish this.

So say I have 100 customers. Then I have 4 Quarters for the year.

I should have 400 records. Each customer should have a value, to show for each Quarter, for the Sales, even if that value is 0.

CustomerQuarterSales
Customer11 (instead of null)0    (instead of null)
Customer1267
Customer1352
Customer1423

I have tried just creating a table with the master list and having a 0 for the Sales field and then Concatenating that to the second table but I don't think that is giving me the results I want.

Any help would be greatly appreciated.

15 Replies
hector_munoz_in
Valued Contributor

Re: Apply value to every row in table

Hi Cristle,

A possible solution would be:

  • 1st load the fact table as usual
  • 2nd concatenate a set of data formed by all the combinations of Client+Quarter+Sales(0)
  • 3nd group by Client+Quarter and sum Sales

In this way you will always have a record per Client and Quarter.

I hope it serves.

Regards,

H

vishsaggi
Esteemed Contributor III

Re: Apply value to every row in table

Can you share a sample if possible? May be use like

IF(Len(Trim(Yourfieldname)) = 0, 0) AS yourfieldname

crystles
Contributor III

Re: Apply value to every row in table

The problem is, I don't have a table with all Customers+Quarters+Sales

I need to just add the Quarter if it's missing with a value of 0 for Sales.... if that's possible...

vishsaggi
Esteemed Contributor III

Re: Apply value to every row in table

Try like this?

IF(Len(Trim(Quarter)) = 0 , 0) AS Sales

crystles
Contributor III

Re: Apply value to every row in table

There are no calculations for the fields. Just two tables.

One table has all the Customers.

The other table has a sales number that's pulled from an Excel document with the Quarter that Sale was in.

But the problem is, there aren't values for every Quarter for every Customer in the excel document... that's why I need to add a value for each Customer, per Quarter.

crystles
Contributor III

Re: Apply value to every row in table

But if the Quarter doesn't exist, it's not going to give me a value for the sales?

vishsaggi
Esteemed Contributor III

Re: Apply value to every row in table

You want to add the value for your sales field or in Quarter field ?

crystles
Contributor III

Re: Apply value to every row in table

I guess, technically both.

If the Quarter isn't in the Excel document, I still need to have a value for that Quarter.

hector_munoz_in
Valued Contributor

Re: Apply value to every row in table

Hi Crystle,

If you do:

TMP_TABLE:
LOAD DISTINCT _KEY

RESIDENT FACT_TABLE;

LEFT JOIN (TMP_TABLE)

LOAD DISTINCT QUARTER,

                              0     AS SALES

RESIDENT FACT_TABLE;

, you will have all the key+quarter combinations and a 0 as sales. Later you concatenate to the fact table and do the group by and sum.

Regards,
H

Community Browser