RE: Insert Empty

[Chanty4u]

Hi all,

I have some excel data and i want to load that data into qlikview.

so my requirment is that.

i want to load all the data  and load   10 empty rows   shud be load   after 25th  record.?

is it possible?

Thanks in advance.

[Digvijay_Singh]

Not sure why you want to do it, check this below -

T1:

First 24

LOAD

  EmpID,

     [Last Name],

     [First Name],

     Title,

     [Hire Date],

     Office,

     Extension,

     [Reports To],

     [Year Salary]

FROM

EmpOff.xls

(biff, embedded labels, table is Employee$);

Concatenate

Load ' ' as A

AutoGenerate(10);

Concatenate

LOAD EmpID,

     [Last Name],

     [First Name],

     Title,

     [Hire Date],

     Office,

     Extension,

     [Reports To],

     [Year Salary]

FROM

EmpOff.xls

(biff, embedded labels, table is Employee$)

Where not exists(EmpID);

Drop field A;

[Digvijay_Singh]

should be 'First 25' in the first script, just re-checked requirement.

[settu_periasamy]

Hi,

May be try

Directory;
T1:
LOAD EmpID,
[Last Name],
[First Name],
Title,
[Hire Date],
Office,
Extension,
[Reports To],
[Year Salary]
FROM
[EmpOff (1).xls]
(biff, embedded labels, table is Employee$) Where RecNo()<=25;

Concatenate
LOAD '' as Temp AutoGenerate 10;

Concatenate(T1)
LOAD EmpID,
[Last Name],
[First Name],
Title,
[Hire Date],
Office,
Extension,
[Reports To],
[Year Salary]
FROM
[EmpOff (1).xls]
(biff, embedded labels, table is Employee$) Where RecNo()>25;


NoConcatenate
Final:
LOAD *,RecNo() as Rec Resident T1;

DROP Table T1;
DROP Field Temp;

[tresesco]

Try like:

LOAD If(RowNo()>25, RowNo()+10, RowNo()) as RowNum,

  EmpID,

    [Last Name],

    [First Name],

    Title,

    [Hire Date],

    Office,

    Extension,

    [Reports To],

    [Year Salary]

FROM

(biff, embedded labels, table is Employee$);

Load

  RecNo()+25 as RowNum,

  '' as EmpID,

    '' as [Last Name],

    '' as [First Name],

    '' as Title,

    '' as [Hire Date],

    '' as Office,

    '' as Extension,

    '' as [Reports To],

    '' as [Year Salary]

AutoGenerate 10;  

[jonathandienst]

I am curious as to  why you want to do this...

[jagan]

Hi,

Try like this

Data:

LOAD RowNo() AS ID,

  EmpID,

     [Last Name],

     [First Name],

     Title,

     [Hire Date],

     Office,

     Extension,

     [Reports To],

     [Year Salary]

FROM

[EmpOff.xls]

(biff, embedded labels, table is Employee$)

WHERE RecNo() <= 25;

Concatenate(Data)

LOAD

  25 + RecNo() AS ID,

' ' AS EmpID,

' ' AS [Last Name],

' ' AS [First Name],

' ' AS Title,

' ' AS [Hire Date],

' ' AS Office,

' ' AS Extension,

' ' AS [Reports To],

' ' AS [Year Salary]

AutoGenerate(10);

Concatenate(Data)

LOAD 35 + RecNo() AS ID,

  EmpID,

     [Last Name],

     [First Name],

     Title,

     [Hire Date],

     Office,

     Extension,

     [Reports To],

     [Year Salary]

FROM

[EmpOff.xls]

(biff, embedded labels, table is Employee$)

WHERE RecNo() > 25;

Regards,

Jagan.

[Chanty4u]

thnks for all...evry one correct.

but my user want  for every  25th record will hve 10 empty rows?

i mean  25 rows

then 10 empty rows

then

60 th rec

then 10 empty rec?

how can i do....there is lot of data i hve?

[jagan]

Hi,

25 & 60 rows are constant?  or in any other place do we need to insert?  For 25 and 60 check below

Data:

LOAD RowNo() AS ID,

  EmpID,

     [Last Name],

     [First Name],

     Title,

     [Hire Date],

     Office,

     Extension,

     [Reports To],

     [Year Salary]

FROM

[EmpOff.xls]

(biff, embedded labels, table is Employee$)

WHERE RecNo() <= 25;

Concatenate(Data)

LOAD

  25 + RecNo() AS ID,

' ' AS EmpID,

' ' AS [Last Name],

' ' AS [First Name],

' ' AS Title,

' ' AS [Hire Date],

' ' AS Office,

' ' AS Extension,

' ' AS [Reports To],

' ' AS [Year Salary]

AutoGenerate(10);

Concatenate(Data)

LOAD 35 + RecNo() AS ID,

  EmpID,

     [Last Name],

     [First Name],

     Title,

     [Hire Date],

     Office,

     Extension,

     [Reports To],

     [Year Salary]

FROM

[EmpOff.xls]

(biff, embedded labels, table is Employee$)

WHERE RecNo() > 25 AND RecNo() <= 60;

Concatenate(Data)

LOAD

  60 + RecNo() AS ID,

' ' AS EmpID,

' ' AS [Last Name],

' ' AS [First Name],

' ' AS Title,

' ' AS [Hire Date],

' ' AS Office,

' ' AS Extension,

' ' AS [Reports To],

' ' AS [Year Salary]

AutoGenerate(10);

Concatenate(Data)

LOAD 35 + RecNo() AS ID,

  EmpID,

     [Last Name],

     [First Name],

     Title,

     [Hire Date],

     Office,

     Extension,

     [Reports To],

     [Year Salary]

FROM

[EmpOff.xls]

(biff, embedded labels, table is Employee$)

WHERE RecNo() > 60;

Regards,

Jagan.

[Anonymous]

Hi Chanty,

try This

T1:

LOAD EmpID,

     [Last Name],

     [First Name],

     Title,

     [Hire Date],

     Office,

     Extension,

     [Reports To],

     [Year Salary],

     RowNo() as rFlag

FROM

(biff, embedded labels, table is Employee$);

T2:

NoConcatenate Load * Resident T1

Where rFlag<=25;

add Load

   '' as EmpID,

      '' as [Last Name],

      '' as [First Name],

      '' as Title,

      '' as [Hire Date],

      '' as Office,

      '' as Extension,

      '' as [Reports To],

      '' as [Year Salary],

      'A'&RecNo() as  rFlag

      AutoGenerate(10);

T2:

Load

     EmpID,

     [Last Name],

     [First Name],

     Title,

     [Hire Date],

     Office,

     Extension,

     [Reports To],

     [Year Salary],

     'B'&rFlag as rFlag

     Resident T1

Where rFlag>=25;

drop table T1;

Hop this will help you....