Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Not applicable

Extrapolating Blank Cells in Excel Tables


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?

CustomerContractSystemID
Sample NameyesXYZ1267809
1554689
ABC1554689

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

~Aly

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Extrapolating Blank Cells in Excel Tables

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

3 Replies

Re: Extrapolating Blank Cells in Excel Tables

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

MVP & Luminary
MVP & Luminary

Re: Extrapolating Blank Cells in Excel Tables

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

Not applicable

Re: Extrapolating Blank Cells in Excel Tables

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