topic Re: RE: Insert Empty in QlikView

RE: Insert Empty

Chanty4u — Fri, 18 Mar 2016 05:06:31 GMT

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.

Re: RE: Insert Empty

Digvijay_Singh — Fri, 18 Mar 2016 05:23:16 GMT

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;

Re: RE: Insert Empty

Digvijay_Singh — Fri, 18 Mar 2016 05:24:42 GMT

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

Re: RE: Insert Empty

settu_periasamy — Fri, 18 Mar 2016 05:25:17 GMT

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;

Re: RE: Insert Empty

tresB — Fri, 18 Mar 2016 05:27:02 GMT

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;

Re: RE: Insert Empty

jonathandienst — Fri, 18 Mar 2016 05:34:19 GMT

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

Re: RE: Insert Empty

jagan — Fri, 18 Mar 2016 05:36:07 GMT

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.

Re: RE: Insert Empty

Chanty4u — Fri, 18 Mar 2016 05:43:20 GMT

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?

Re: RE: Insert Empty

jagan — Fri, 18 Mar 2016 05:50:48 GMT

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.

Re: RE: Insert Empty

Anonymous — Fri, 18 Mar 2016 05:53:12 GMT

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

Re: RE: Insert Empty

Chanty4u — Fri, 18 Mar 2016 05:53:28 GMT

constant means fo evry after 25th record. any for loop?

Re: RE: Insert Empty

Chanty4u — Fri, 18 Mar 2016 05:55:31 GMT

i want evry 25th after 10rows shud be '0'

is there any posibilty?

Re: RE: Insert Empty

jagan — Fri, 18 Mar 2016 05:56:32 GMT

Constant means if you just want this 25 and 60 rows use the code posted in previous thread. If you want for every 25th row like 25, 50, 75, 100 etc. then use FOR loop.

Regards,

Jagan.

Re: RE: Insert Empty

Chanty4u — Fri, 18 Mar 2016 05:57:36 GMT

is there any other posibilty while loading the data?

wizard?

Re: RE: Insert Empty

jagan — Fri, 18 Mar 2016 06:00:01 GMT

There is no wizard check the help file for syntax. What is your requirement acutally? You want to insert 10 empty records after every 25 rows?

Regards,

jagan.

Re: RE: Insert Empty

Chanty4u — Fri, 18 Mar 2016 06:01:04 GMT

exactly..jagan

Re: RE: Insert Empty

jagan — Fri, 18 Mar 2016 06:31:47 GMT

Hi,

Check this

Temp:

LOAD RowNo() AS ID

FROM

[EmpOff.xls]

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

LET vTotalRows = NoOfRows('Temp');

LET vNoofIterations = Ceil(vTotalRows/25);

LET vStartRow = 0;

LET vRowsHandler = 25;

DROP TABLE Temp;

FOR Index = 1 TO vNoofIterations

LET vRowCount = vStartRow + ((Index - 1) * 10);

Data:

LOAD ($(vRowCount) + 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() > $(vStartRow) AND RecNo() <= $(vRowsHandler);

LET vRowCount = vRowCount + 25;

Concatenate(Data)

LOAD

($(vRowCount) + RowNo()) 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);

LET vRowsHandler = vRowsHandler + 25;

LET vStartRow = vStartRow + 25;

Re: RE: Insert Empty

Chanty4u — Fri, 18 Mar 2016 06:36:40 GMT

awesome bro....

Thanks

this is exctly need.