topic Re: Mapping issue in QlikView

Mapping issue

buzzy996 — Fri, 11 Aug 2017 09:30:06 GMT

Hello quickers,

can you help how to map these two tables with out using joins.

Table 1:

Column 1: BillNum

-------

R847159 1

R839996/1

R637077 and R699751

R809861 / R816528

R848984 Item no: 8

R758771, R758772, R758773, and R789247

R578433 R545966 R574458 R568711

No.: R848984 Item no: 44

R848914

Column 2:Date

DR-07/05/2017-7949

DR-17/06/2017-7950

DR-19/06/2017-7951

DR-26/01/2017-7952

DR-22/06/2017-7953

DR-24/07/2017-7954

DR-21/06/2017-7923

DR-29/07/2017-7934

DR-19/07/2017-7934

Table 2:

Column 1: BillNum

--------

R847159

R839996

R699751

R816528

R848984

R758772

R574458

R848984

Column 2:EXDate

DR-07/05/2017-7949

DR-17/06/2017-7950

DR-19/06/2017-7951

DR-26/01/2017-7952

DR-22/06/2017-7953

DR-24/07/2017-7954

DR-21/06/2017-7923

DR-29/07/2017-7934

Question: need to map table 2_column 1 with table table 1_column 1 and expected output is table 2 Column 2.

Thanks

Re: Mapping issue

Kushal_Chawda — Fri, 11 Aug 2017 09:40:12 GMT

which number need to take from this string R758771, R758772, R758773, and R789247?

Re: Mapping issue

buzzy996 — Fri, 11 Aug 2017 09:44:47 GMT

hi,second one "R758772",thank u

Re: Mapping issue

antoniotiman — Fri, 11 Aug 2017 10:13:20 GMT

Hi Shiva,

like this ?

Re: Mapping issue

buzzy996 — Fri, 11 Aug 2017 10:29:06 GMT

hi,No.

expected result is table 2 with both the columns mentioned above.

thx

Re: Mapping issue

antoniotiman — Fri, 11 Aug 2017 10:45:20 GMT

Can You be more specific ? Below Final table from joined tables

Re: Mapping issue

buzzy996 — Fri, 11 Aug 2017 11:01:31 GMT

Hi,This is expected output.

BillNum	EXDate
R847159	DR-07/05/2017-7949
R839996	DR-17/06/2017-7950
R699751	DR-19/06/2017-7951
R816528	DR-26/01/2017-7952
R848984	DR-22/06/2017-7953
R758772	DR-24/07/2017-7954
R574458	DR-21/06/2017-7923
R848984	DR-29/07/2017-7934

Re: Mapping issue

Chanty4u — Fri, 11 Aug 2017 11:07:05 GMT

Hi,

check this may be it will help

concatenate

Re: Mapping issue

buzzy996 — Fri, 11 Aug 2017 11:14:47 GMT

hi,thanks for the above,but seems to be thr is no relation between these two cases.

Re: Mapping issue

antoniotiman — Fri, 11 Aug 2017 11:17:18 GMT

This ?

Re: Mapping issue

buzzy996 — Fri, 11 Aug 2017 11:27:00 GMT

hi,yes.can u share the solution?..i can check with mine and confirm.

hope u arrived with the above solution without direct joining of the tables (bcz,i do not like to use direct joine in this case)

Re: Mapping issue

antoniotiman — Fri, 11 Aug 2017 11:29:47 GMT

Table1:
LOAD BillNum,SubField(BillNum1,' ') as BillNum1,Date;
LOAD BillNum,Replace(Replace(BillNum,'/',' '),',',' ') as BillNum1,SubField(Date,'-',2) as Date; // Date is optional
LOAD * Inline [
BillNum|Date
R847159 1|DR-07/05/2017-7949
R839996/1|DR-17/06/2017-7950
R637077 and R699751|DR-19/06/2017-7951
R809861 / R816528|DR-26/01/2017-7952
R848984 Item no: 8|DR-22/06/2017-7953
R758771, R758772, R758773, and R789247|DR-24/07/2017-7954
R578433 R545966 R574458 R568711|DR-21/06/2017-7923
No.: R848984 Item no: 44|DR-29/07/2017-7934
R848914|DR-19/07/2017-7934
](delimiter is '|');

Left Join
LOAD * Inline [
BillNum1,ExDate
R847159,DR-07/05/2017-7949
R839996,DR-17/06/2017-7950
R699751,DR-19/06/2017-7951
R816528,DR-26/01/2017-7952
R848984,DR-22/06/2017-7953
R758772,DR-24/07/2017-7954
R574458,DR-21/06/2017-7923
R848984,DR-29/07/2017-7934];
Table2:
NoConcatenate
LOAD ExDate,BillNum1 as BillNum
Resident Table1 Where Len(Trim(ExDate)) > 0;
DROP Table Table1;

Re: Mapping issue

buzzy996 — Fri, 11 Aug 2017 11:51:28 GMT

hi thanks for the above,seems u assumed "EXDate" as input column from table 2 but tht's not correct.

i'm expecting that's the output (EXDate field should be generate through "Date column 1 from table 1 by mapping")

any help?

Re: Mapping issue

antoniotiman — Fri, 11 Aug 2017 11:57:40 GMT

Ok, see new attachment.

Re: Mapping issue

effinty2112 — Fri, 11 Aug 2017 12:40:09 GMT

Hi Shiva,

We can get a straight table like this:

BillNum	EXDate
No.: R848984 Item no: 44	DR-22/06/2017-7953 & DR-29/07/2017-7934
R578433 R545966 R574458 R568711	DR-21/06/2017-7923
R637077 and R699751	DR-19/06/2017-7951
R758771, R758772, R758773, and R789247	DR-24/07/2017-7954
R809861 / R816528	DR-26/01/2017-7952
R839996/1	DR-17/06/2017-7950
R847159 1	DR-07/05/2017-7949
R848984 Item no: 8	DR-22/06/2017-7953 & DR-29/07/2017-7934

EXDate is the expression:

Aggr(if(SubStringCount(BillNum,BillNum1)=1,Concat(EXDate,' & ')),BillNum,BillNum1)

Nothing fancy in the script, just renamed the BillNum field in Table2 to BillNum1:

Table1:

LOAD * INLINE [

BillNum, Date

R847159 1, DR-07/05/2017-7949

R839996/1, DR-17/06/2017-7950

R637077 and R699751, DR-19/06/2017-7951

R809861 / R816528, DR-26/01/2017-7952

R848984 Item no: 8, DR-22/06/2017-7953

"R758771, R758772, R758773, and R789247", DR-24/07/2017-7954

R578433 R545966 R574458 R568711, DR-21/06/2017-7923

No.: R848984 Item no: 44, DR-29/07/2017-7934

R848914, DR-19/07/2017-7934

];

Table2:

LOAD * INLINE [

BillNum1, EXDate

R847159, DR-07/05/2017-7949

R839996, DR-17/06/2017-7950

R699751, DR-19/06/2017-7951

R816528, DR-26/01/2017-7952

R848984, DR-22/06/2017-7953

R758772, DR-24/07/2017-7954

R574458, DR-21/06/2017-7923

R848984, DR-29/07/2017-7934

];

Cheers

Andrew

With Suppress zero values unchecked:

BillNum	EXDate
No.: R848984 Item no: 44	DR-22/06/2017-7953 & DR-29/07/2017-7934
R578433 R545966 R574458 R568711	DR-21/06/2017-7923
R637077 and R699751	DR-19/06/2017-7951
R758771, R758772, R758773, and R789247	DR-24/07/2017-7954
R809861 / R816528	DR-26/01/2017-7952
R839996/1	DR-17/06/2017-7950
R847159 1	DR-07/05/2017-7949
R848914
R848984 Item no: 8	DR-22/06/2017-7953 & DR-29/07/2017-7934

No match for R848914.

Re: Mapping issue

buzzy996 — Fri, 11 Aug 2017 13:08:15 GMT

thank u,still using EXDate column in script.

i just dropped that column & whr clause and reloaded data ..getting many records.

can u check?

Re: Mapping issue

buzzy996 — Fri, 11 Aug 2017 13:11:08 GMT

thank u, but EXDate is not my source column ,tht's expected out column.

we have to map through BillNum.

any help?

Re: Mapping issue

antoniotiman — Fri, 11 Aug 2017 13:17:46 GMT

Last Step

Table2:
NoConcatenate
LOAD BillNum1 as BillNum,Date
Resident Table1 Where Len(Trim(ExDate)) > 0;
DROP Table Table1;

Note: I use ExData as Flag to purge Rows not joined.

Re: Mapping issue

buzzy996 — Fri, 11 Aug 2017 13:35:02 GMT

hi,thank u, but my question is ExDate is not data source column,then how we can use for where clause condition?

Re: Mapping issue

antoniotiman — Fri, 11 Aug 2017 13:44:52 GMT

See Attachment.

ExData not loaded (You can purge in Inline Load)