Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

satishkamath
Contributor II

Adding/Replicate column

Hi Community

I have below two table (F1 is Fact and R1 is Reference)

Capture.PNG

I only need to column SID to the table F1 for given CDate and FW. Like

For 1st row in F1 will be like                          For 2nd row in F1 will be like         For 3rd row in F1 will be like

30-Sep-14,  CCAR, 101, 1                            30-Sep-14,  DFAST, 104, 6           31-Dec-14,  DFAST, 125, 9

30-Sep-14,  CCAR, 101, 2                            30-Sep-14,  DFAST, 104, 7           31-Dec-14,  DFAST, 125, 10

30-Sep-14,  CCAR, 101, 3                            30-Sep-14,  DFAST, 104, 8           31-Dec-14,  DFAST, 125, 11

30-Sep-14,  CCAR, 101, 4

30-Sep-14,  CCAR, 101, 5

And all in one table.

I tried left join but unable to apply two resident tables with filter condition.

 

F1:

LOAD * INLINE [
CDate, FW, AVal
30-Sep-14, CCAR, 101
30-Sep-14, DFAST, 104
31-Dec-14, DFAST, 125
]
;

R1:
LOAD * INLINE
[
CDate, FW, Syno, SID
30-Sep-14, CCAR, F B, 1
30-Sep-14, CCAR, F A, 2
30-Sep-14, CCAR, F SA, 3
30-Sep-14, CCAR, B B, 4
30-Sep-14, CCAR, B A, 5
30-Sep-14, DFAST, F B, 6
30-Sep-14, DFAST, F A, 7
30-Sep-14, DFAST, F SA, 8
31-Dec-14, DFAST, B B, 9
31-Dec-14, DFAST, B A, 10
31-Dec-14, DFAST, B SA, 11
]
;

Two inline tables definition attached for reference

Thanking you

9 Replies
Highlighted
anbu1984
Honored Contributor III

Re: Adding/Replicate column

Check this app

F1:

LOAD * INLINE [

CDate, FW, AVal

30-Sep-14, CCAR, 101

30-Sep-14, DFAST, 104

31-Dec-14, DFAST, 125

];

Left Join(F1)

R1:

LOAD * INLINE

[

CDate, FW, Syno, SID

30-Sep-14, CCAR, F B, 1

30-Sep-14, CCAR, F A, 2

30-Sep-14, CCAR, F SA, 3

30-Sep-14, CCAR, B B, 4

30-Sep-14, CCAR, B A, 5

30-Sep-14, DFAST, F B, 6

30-Sep-14, DFAST, F A, 7

30-Sep-14, DFAST, F SA, 8

31-Dec-14, DFAST, B B, 9

31-Dec-14, DFAST, B A, 10

31-Dec-14, DFAST, B SA, 11

];

Partner
Partner

Re: Adding/Replicate column

Hi,

Try this

LOAD * INLINE [

CDate, FW, AVal

30-Sep-14, CCAR, 101
30-Sep-14, DFAST, 104

31-Dec-14, DFAST, 125

]
;





LEFT JOIN(F1)

LOAD * INLINE

[

CDate, FW, Syno, SID

30-Sep-14, CCAR, F B, 1

30-Sep-14, CCAR, F A, 2
30-Sep-14, CCAR, F SA, 3

30-Sep-14, CCAR, B B, 4

30-Sep-14, CCAR, B A, 5

30-Sep-14, DFAST, F B, 6

30-Sep-14, DFAST, F A, 7
30-Sep-14, DFAST, F SA, 8

31-Dec-14, DFAST, B B, 9

31-Dec-14, DFAST, B A, 10
31-Dec-14, DFAST, B SA, 11
]
WHERE Exists (CDate) ;

Partner
Partner

Re: Adding/Replicate column

F1:

LOAD * INLINE [

]

Left Join(F1)

R1:

LOAD * INLINE [

]

should work

satishkamath
Contributor II

Re: Adding/Replicate column

Hi Gabriel

Your anser is helpful but the question is WHERE Exist(CDate) is reqired?

Without this I also achieve result.

Thanking you

satishkamath
Contributor II

Re: Adding/Replicate column

Hi Anbu

Your suggestion works greatly, but in addition to this if there are two resident tables. Can we add filter on those?

Thanking you

anbu1984
Honored Contributor III

Re: Adding/Replicate column

What is your filter?

Partner
Partner

Re: Adding/Replicate column

If you have two tables to be joined , then you can add flag to differentiate...

Partner
Partner

Re: Adding/Replicate column

Hi Satish,

Use "LEFT JOIN" you will achieve your requirement.

Regards,

Nagarjuna

satishkamath
Contributor II

Re: Adding/Replicate column

Manoj

I appreciate.

Your suggestion make sense so I added

Thanks