Re: Adding/Replicate column - Qlik Community

Anonymous · ‎2015-04-14

Hi Community

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

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

anbu1984 · ‎2015-04-14

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

];

Gabriel · ‎2015-04-14

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) ;

manojkulkarni · ‎2015-04-14

F1:

LOAD * INLINE [

]

Left Join(F1)

R1:

LOAD * INLINE [

]

should work

Anonymous · ‎2015-04-14

Hi Gabriel

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

Without this I also achieve result.

Thanking you

Anonymous · ‎2015-04-14

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 · ‎2015-04-14

What is your filter?

manojkulkarni · ‎2015-04-14

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

nagarjuna_kotha · ‎2015-04-14

Hi Satish,

Use "LEFT JOIN" you will achieve your requirement.

Regards,

Nagarjuna

Anonymous · ‎2015-04-14

Manoj

I appreciate.

Your suggestion make sense so I added

Thanks