using Peek/Previous function : Can we do copy/paste of values to next row

[#1]

Hi,

I have a requirement which can be achieved by using peek/Previous function. But I couldnt able to do successfully. Is there any other option available for it.

Inputs: One Fact table ( Brand, Type, Country_key, Customer_Key, Qty), First Dimension table ( Country_key, Sold Country), Second Dimesion table ( Customer_key, Customer Name)

Sample data is captured in excel and same is loaded into Qlikview document.

In my chart, I have Columns like Brand, Type, Sold Country, Customer Name and Quantity.

Brand can be same for 2 or more types of cars, but the country_key and customer_key will vary. There are few keys missing in the fact tables , bdue to which null's are appearing in my straight table chart.

Now , I dont want to see those null values, Instead I wanted to copy paste the Customer and Country names for NULL values if there exists a records with values for the same brand , even though type is different.

Chart O/p in excel...

Brand	Type	Customer_Name	Sold_Country	Qty
				-
Ford	Fiesta	Bill	Singapore	950
Ford	Icon	Jhon	Canada	480
Honda	City	Hodge	China	776
Honda	Civic	-	-	480
Honda Special	Accord	Smith	Mexico	650
Honda Special	Jazz	-	-	320
Hyundai	i10	James	India	100
Hyundai	i20	-	-	10
Hyundai Special	Santro	-	-	40
Hyundai Special	Verna	Jone	Europe	80
Maruthi	Alto	Criseller	Australia	500
Maruthi	K10	-	-	70
Maruthi Special	Omini	Mike	Brazil	90
Maruthi1	Ritz	Juggal	South Africa	9
Maruthi1	Wagnor	Christin	Qatar	1200
Maruthi2	Eeco	-	-	5
Maruthi2	Swift	Marry	Pakistan	980

For eg: HOnda-->City has Sold to country and customer values.. It has to be copy pasted to Honda--> Civic since it has null values...

similarlly Honda Special-->Accord has sold to country and customer values. where as Honda special-->Jazz doesnt have values. So , above values should be paste here... O/p should be

Honda Special	Accord	Smith	Mexico	650
Honda Special	Jazz	Smith	Mexico	320

Can this is achived in the chart using expresssion??

Many thanks!

Vinod

[pat_agen]

Hi,

don't know about doing it in a chart but certainly you can do this at load time.

In the scriopt dialogue use the wizard available through the "table files..." button in the "Data from files" panel at the bottom of the screen. In the wizard you have the "Transform" button.

in this dialogue you have a fill option and you can give rules on how to complete missing data.

the code this generates will be similar to this:

filters(Replace(2, top, StrCnd(null)))

So sort your input data correctly and tell qlikview how to fill in the missing information.

hope this helps.

[#3]

Hi PatAgen,

Thanks for your quick reply..

The above logic will replace the NULL values either by Top or bottom or left or right cell values if the cell condition is set to "Is empty". It will not check for any other condition.

But in my scenario, The NULL values should be replaced by a non null value above to it, if it exists for the same brand name, else it should remain as "NULL'..... Hope I am clear

How to do in load script??

Thanks.Vinod

[pat_agen]

Hi,

see attached qvw.

[#5]

So that qvw you posted shows the previous one if blank, but what if multiple are blank in a row? Do you know if there is any way to show the most recent (last read?) row that has a value? Sorry didn't mean to hijack this thread but maybe that relates to what you are doing.

[pat_agen]

hi Trent,

the solution ony works if the previous record has country, customer data, subsequent records wouldn't work but then it can be argued that this is a question of source data. If the input is an aggeragted summary of volumes by Brand, Type, customer_name and country then we shoudl probably only expect one null record per brand/type couple.

If more null values in customer name and country were to be expected then you would have to further in the script, maybe rereading the data again until all missing rows have been filled in where necessary.

[#7 · Accepted solution]

Now the copy paste functionality is acheived by using lookup and mapping functions. See the below code..

Dim1:

LOAD

Country_Key

,

Sold_Country

FROM

(

biff, embedded labels, table is Sheet2$)

;

Dim2:

LOAD

Customer_ID

,

Customer_Name

FROM

(

biff, embedded labels, table is Sheet3$)

;

Mapping_Customer:

Mapping

Load

distinct

Brand

,

Customer_ID

from

(

biff, embedded labels, table is Sheet1$) where not isnull(Customer_ID

)

or

Customer_ID <>''

;

Mapping_Country:

Mapping

Load

distinct

Brand

,

Country_Key

from

(

biff, embedded labels, table is Sheet1$) where not isnull(Country_Key

)

or

Country_Key <>''

;

Fact:

LOAD

Brand

,

Type

,

Country_Key

,

Customer_ID

,

Qty

,

lookup

('Sold_Country','Country_Key',if(isnull(Country_Key), applymap('Mapping_Country', Brand),Country_Key) ,'Dim1') as Sold_Country

,

lookup

('Customer_Name','Customer_ID',if(isnull(Customer_ID), applymap('Mapping_Customer', Brand),Customer_ID),'Dim2') as

Customer_Name

FROM

(

biff, embedded labels, table is Sheet1$)

;

drop

table

Dim1;

drop

table

Dim2;