topic Re: Apply value to every row in table in QlikView

Apply value to every row in table

crystles — Thu, 16 Feb 2017 21:20:17 GMT

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.

Customer	Quarter	Sales
Customer1	1 (instead of null)	0 (instead of null)
Customer1	2	67
Customer1	3	52
Customer1	4	23

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.

Re: Apply value to every row in table

hector_munoz — Thu, 16 Feb 2017 21:26:33 GMT

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,

Re: Apply value to every row in table

vishsaggi — Thu, 16 Feb 2017 21:31:05 GMT

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

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

Re: Apply value to every row in table

crystles — Thu, 16 Feb 2017 21:31:32 GMT

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...

Re: Apply value to every row in table

vishsaggi — Thu, 16 Feb 2017 21:34:05 GMT

Try like this?

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

Re: Apply value to every row in table

crystles — Thu, 16 Feb 2017 21:34:06 GMT

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.

Re: Apply value to every row in table

crystles — Thu, 16 Feb 2017 21:34:59 GMT

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

Re: Apply value to every row in table

vishsaggi — Thu, 16 Feb 2017 21:35:52 GMT

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

Re: Apply value to every row in table

crystles — Thu, 16 Feb 2017 21:37:04 GMT

I guess, technically both.

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

Re: Apply value to every row in table

hector_munoz — Thu, 16 Feb 2017 21:37:07 GMT

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

Re: Apply value to every row in table

vishsaggi — Thu, 16 Feb 2017 21:51:19 GMT

How do you identify which quarter you want if missing?

Re: Apply value to every row in table

crystles — Thu, 16 Feb 2017 21:58:27 GMT

That's the problem.... I don't know either?

Re: Apply value to every row in table

vishsaggi — Thu, 16 Feb 2017 22:01:32 GMT

Is there any date field coming in that excel sheet?

Re: Apply value to every row in table

jackhertel — Fri, 17 Feb 2017 16:12:08 GMT

This one is easier to do with a Cartesian join.

//100 Names with a few having sales

t1:

LOAD * INLINE [

Name, Sales

Aaliyah Barr

Aaron Roac, 70

Abagail Serran, 300

Abbey Stei, 100

Abbigail Gallego, 1000

Abby Villega

......

Barrett Edward

];

Quarters:

LOAD * INLINE [

Quarter

];

//Get the unique list of names

Sales:

Load Distinct Name

Resident t1;

//Use a cartesian product to create 400 rows (100 * 4) bu left joining the Quarters table with no linking field This makes your 400 rows

Left Join (Sales)

Load Quarter

Resident Quarters;

//Add in the sales I spread it out 1/4 to each quarter. The ones with no sales get a zero

Left Join (Sales)

Load Name,

Sales/4

Resident t1;

Re: Apply value to every row in table

jackhertel — Fri, 17 Feb 2017 16:13:54 GMT

Sorry, you wanted zeros not nulls. Just add a column to the quarter in line load for nosales with a 0 in it. left join that and add it to sales to get sales with a zero. I will update app

Re: Apply value to every row in table

jackhertel — Fri, 17 Feb 2017 16:17:46 GMT

here you go.

Drop all of the junk table to get rid of the synthetic keys.

Enjoy,

Jack