topic Re: How to Map each value with other Values in QlikView

How to Map each value with other Values

Vinod — Wed, 25 Nov 2020 16:16:04 GMT

Hi all

Source Table:

REG NO	VALUE
A1	20171103-E0023-0 20171110-E0023-0
B1	20161125-E0034-0 20171130-30022-0
C1	20170717-C0036-0 20180226-E0052-0
D1	20160510-E0049-0 20160519-C0043-0 20160527-E0056-0
E1	20170330-E0059-0 20170824-A0018-0 20170922-30011-0 20171025-E0039-0

Above I have created one table and in that tables i want to Map the REG NO with each value of the VALUE column. Means I want output like below mentioned table.

REG NO	VALUE
A1	20171103-E0023-0
A1	20171110-E0023-0
B1	20161125-E0034-0
B1	20171130-30022-0
C1	20160510-E0049-0
C1	20160519-C0043-0
C1	20160527-E0056-0
D1	20160510-E0049-0
D1	20160519-C0043-0
D1	20160527-E0056-0
E1	20170330-E0059-0
E1	20170824-A0018-0
E1	20170922-30011-0
E1	20171025-E0039-0

Please anyone reply

Regards,

Vinod

Re: How to Map each value with other Values

vishsaggi — Sun, 05 Aug 2018 01:04:29 GMT

‌WHat is your source Excel, csv or SQL? What happens when you use straight Table or table box? Check and let me know?

Re: How to Map each value with other Values

BalaBhaskar_Qlik — Sun, 05 Aug 2018 06:00:20 GMT

Try below scenarios:

This is after data load

Region:

Load

[Reg No],

Value

from Region.csv;

NoConcatenate

Temp:

Load

If(IsNull[Reg No],Above([Reg No]),[Reg No]) as [Reg No],

Value

Resident Region;

Drop table Region;

This is, at the time of data load, replace null row values with above row value.

Region:

LOAD [Reg No],

Value

FROM [..\..\data\Edu\Data_03162012.xls]

(biff, embedded labels, table is Sheet1$,

filters(Replace(1, top, StrCnd(null))));

Re: How to Map each value with other Values

Vinod — Sun, 05 Aug 2018 06:03:36 GMT

My Source is Excel File. Actually i am new to QlikView. So Please explain all the scenarioa's with Excel,CSV and SQL.

Re: How to Map each value with other Values

BalaBhaskar_Qlik — Sun, 05 Aug 2018 06:11:48 GMT

Try like this:

From File wizard - Select Excel file - Next - Click - Enable transformation step - Click - Fill tab - Click on left side - Fill tab - Target Column(1 or 2 or... Column Number) - Cell Condition - Cell value - Is empty - OK, Fill Type - Above(Radio Button) - Ok - Next - Next - Check the script - Finish.

Run the script. Data reloaded.

Re: How to Map each value with other Values

Vinod — Sun, 05 Aug 2018 06:16:43 GMT

it's not working sir.

Re: How to Map each value with other Values

BalaBhaskar_Qlik — Sun, 05 Aug 2018 06:19:39 GMT

Did you tried like this:

This is after data load from excel:

Region:

Load

[Reg No],

Value

from Region.csv;

NoConcatenate

Temp:

Load

If(IsNull[Reg No],Above([Reg No]),[Reg No]) as [Reg No],

Value

Resident Region;

Drop table Region;

Re: How to Map each value with other Values

Vinod — Sun, 05 Aug 2018 06:25:46 GMT

I am having sample data.

Above table is my source table. I want to output like below table.

Please send me sample application.

Re: How to Map each value with other Values

BalaBhaskar_Qlik — Sun, 05 Aug 2018 06:38:51 GMT

May be like this:

Region:

Load

[Reg No],

Value

from Region.csv;

NoConcatenate

Temp:

Load

& chr(10) & SubField([Reg No],' '3) as [Reg No], //Or below one

Replace([Reg No],' ',chr(10)) as [Reg No],

Value

Resident Region;

Drop table Region;

Re: How to Map each value with other Values

Vinod — Sun, 05 Aug 2018 07:19:32 GMT

I tried above code it is also not working sir. Please send me your application.

Re: How to Map each value with other Values

BalaBhaskar_Qlik — Sun, 05 Aug 2018 10:30:30 GMT

Use SubField(), to split the data based on delimiter.

Region:

Load

[Reg No],

Value

from Region.csv;

NoConcatenate

Temp:

Load

Subfield([Reg No],' ') as [Reg No],

Value

Resident Region;

Drop table Region;

Re: How to Map each value with other Values

vishsaggi — Sun, 05 Aug 2018 13:56:14 GMT

‌canyou share your excel file if possible pls to look into. Using advanced tab you can attch the file.

Re: How to Map each value with other Values

Vinod — Sun, 05 Aug 2018 17:46:17 GMT

Sorry sir, I am unable to find attaching link here.

Re: How to Map each value with other Values

vishsaggi — Mon, 06 Aug 2018 00:39:40 GMT

You can upload your excel file by clicking on reply to one of my messages then on right top corner you can see use advanced editor like below:

Then once you click on that a new page will open then you can see attach icon on bottom right corner. like below:

Re: How to Map each value with other Values

Vinod — Mon, 06 Aug 2018 03:34:09 GMT

In my screen it is not showing. Sir.

Re: How to Map each value with other Values

Anil_Babu_Samineni — Mon, 06 Aug 2018 04:28:05 GMT

Try to share excel file? Whether data store in cell or row?

Re: How to Map each value with other Values

pooja_prabhu_n — Mon, 06 Aug 2018 09:23:56 GMT

Hi,

can you share sample data?

You can share your excel file using Advanced editor.

Re: How to Map each value with other Values

tamilarasu — Mon, 06 Aug 2018 09:38:14 GMT

Hi Vinod,

I believe that you know how to load the data from excel to QlikView. If so, try this,

Data:
LOAD
[REG NO],
SubField(VALUE, CHR(10)) as VALUE
From Excel;

Here, Excel is your file path.

Re: How to Map each value with other Values

thannila — Mon, 06 Aug 2018 09:49:24 GMT

Try this below code:

Load

"REG NO",

SUBFIELD(replace(VALUE,left(purgechar(VALUE,'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789-'),1),','),',') AS RESULT

RESIDENT Table;

Re: How to Map each value with other Values

Vinod — Mon, 06 Aug 2018 10:11:37 GMT

Hi Tamil Arasu sir, It's working.

Thank you.