topic Re: Extrapolating Blank Cells in Excel Tables in QlikView

Extrapolating Blank Cells in Excel Tables

Thu, 19 Feb 2015 01:06:27 GMT

Hi,

Wondering if anyone had a great solution for extrapolating data into blank cells from a table within Excel? I know what the data should be in those blank cells (please see example below) but the user has not filled them in. In the example below you can see that the customer has a contract for 3 systems. It is the same customer across the three rows and the same system with a different ID for two rows. How can I get Qlikview to populate the customer name and link it to those systems for a count?

Customer	Contract	System	ID
Sample Name	yes	XYZ	1267809
			1554689
		ABC	1554689

I am a very new user to Qlikview so any advice would be greatly appreciated! Thanks in Advance.

~Aly

Re: Extrapolating Blank Cells in Excel Tables

Miguel_Angel_Baeyens — Thu, 19 Feb 2015 02:02:32 GMT

Hello Aly,

Something like this?

Source:

LOAD Customer, 
      Contract, 
      System, 
      ID,
      RowNo() AS Dummy
 FROM
 
 (ooxml, embedded labels, table is Sheet1);
 
 Sorted:
 LOAD If(Len(Customer) = 0, Peek('Customer', -1), Customer) AS Customer,
 If(Len(Contract) = 0, Peek('Contract', -1), Contract) AS Contract,
 If(Len(System) = 0, Peek('System', -1), System) AS System,
 If(Len(ID) = 0, Peek('ID', -1), ID) AS ID
 RESIDENT Source;
 
 DROP TABLE Source;

Miguel

Re: Extrapolating Blank Cells in Excel Tables

rwunderlich — Thu, 19 Feb 2015 02:05:03 GMT

You can do the Fill with a filter parameter in the LOAD. TO build the Filter, when in the Table File wizard in the Script Editor,

1. Press Next.

2. Press "Enable Transformation step"

3. Select the Fill tab,

You can prrobably figure it out by playing with it but press the Help button to get some instructions. If you search for "Transformation Fill" on this forum for some more examples.

-Rob

Re: Extrapolating Blank Cells in Excel Tables

Thu, 19 Feb 2015 03:01:38 GMT

Thank you both for your quick replies! Using the Table file wizard worked a treat and is super easy.