Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
qvqfqlik
Creator
Creator

need help with creating data

Please help with getting a row to be created .

in the final table, I need to insert Need_ids  from both the tables

here is the sample data

ID_NAME Table:

Need_ID   field_name

1                 n1

2               n2

3                 n3

4               n4

5               n5

6               n6

7                 n7

8               n8

9               n9

10               n10

11               n11

12               n12

13               n13

14               n14

15               n15

Table1:

Need_ID   state_ID   country_id        value        

1                s1                 c1                   abc          

2                s1                c1                   def

3                s1                c1                   ghi

4                s1                c1                   jkl

5                s1                c1                   mno


1               s3               c1                    abc

2               s3                c1                    def

13             s3               c1                      bnm


1                s2               c2                   qqq

2               s2                c2                   sss

3                s2                c2                   ggg


3               s4               c2                    lorp

14              s4             c2                       nevs


2          s5               c2                            sss

12         s5               c2                         uicvb

14          s5               c2                         8feiru9u9


Table2:

Need_ID      country_id      value

1                c1                     abc

2                c1                     def

5                c1                     mno

6                c1                     pqr

7                c1                     stu

8                c1                     vwx


2                c2                   sss

5                c2                     ioio


Need final table data as below

Final table:

Need_ID    state_id   country_id    value        field_name

1                s1                c1           abc               n1

2                s1                c1           def               n2

3                s1                c1           ghi               n3

4                s1                c1           jkl                n4

5                s1                c1           mno            n5

6                s1                c1           pqr              n6

7                s1                c1           stu               n7

8                s1                c1           vwx               n8


1                s2                c2         qqq              n1

2                s2                c2         sss               n2

3                s2                c2         ggg               n3

5                s2               c2           ioio               n5


1               s3               c1             abc               n1

2               s3                c1           def               n2

13             s3               c1             bnm          n13

5               s3                c1            mno          n5

6               s3                c1             pqr          n6

7               s3                c1             stu          n7

8              s3                c1              vwx          n8


3               s4               c2                lorp       n3

14             s4                c2               nevs     n14

2                s4               c2               sss          n2

5                s4               c2               ioio          n5


2               s5               c2                  sss           n2

12              s5               c2                 uicvb          n12

14               s5               c2                8feiru9u9          n14

5                  s5             c2                 ioio               n5


1 Solution

Accepted Solutions
sunny_talwar

Try this

Table1:

LOAD * INLINE [

    Need_ID, state_ID, country_id, value

    1, s1, c1, abc

    2, s1, c1, def

    3, s1, c1, ghi

    4, s1, c1, jkl

    5, s1, c1, mno

    1, s3, c1, abc

    2, s3, c1, def

    13, s3, c1, bnm

    1, s2, c2, qqq

    2, s2, c2, sss

    3, s2, c2, ggg

    3, s4, c2, lorp

    14, s4, c2, nevs

    2, s5, c2, sss

    12, s5, c2, uicvb

    14, s5, c2, 8feiru9u9

];


Table2:

LOAD * INLINE [

    Need_ID, country_id, value

    1, c1, abc

    2, c1, def

    5, c1, mno

    6, c1, pqr

    7, c1, stu

    8, c1, vwx

    2, c2, sss

    5, c2, ioio

];


Left Join (Table2)

LOAD Distinct country_id,

state_ID

Resident Table1;


Concatenate(Table1)

LOAD *

Resident Table2;


DROP Table Table2;


Capture.PNG

View solution in original post

6 Replies
sunny_talwar

So, we are only filling in state_id here? but this seems like we are looking at only one country_id with one state_id... it might help if you share some more rows of data with multiple country_ids and state_ids with the expected output to show the general idea.

sunny_talwar

Try this

Table1:

LOAD * INLINE [

    Need_ID, state_ID, country_id, value

    1, s1, c1, abc

    2, s1, c1, def

    3, s1, c1, ghi

    4, s1, c1, jkl

    5, s1, c1, mno

    1, s3, c1, abc

    2, s3, c1, def

    13, s3, c1, bnm

    1, s2, c2, qqq

    2, s2, c2, sss

    3, s2, c2, ggg

    3, s4, c2, lorp

    14, s4, c2, nevs

    2, s5, c2, sss

    12, s5, c2, uicvb

    14, s5, c2, 8feiru9u9

];


Table2:

LOAD * INLINE [

    Need_ID, country_id, value

    1, c1, abc

    2, c1, def

    5, c1, mno

    6, c1, pqr

    7, c1, stu

    8, c1, vwx

    2, c2, sss

    5, c2, ioio

];


Left Join (Table2)

LOAD Distinct country_id,

state_ID

Resident Table1;


Concatenate(Table1)

LOAD *

Resident Table2;


DROP Table Table2;


Capture.PNG

qvqfqlik
Creator
Creator
Author

Thanks Sunny, this is working... but it is taking very long time. Probably because of Left join.

Can you please suggest on this.

sunny_talwar

Don't know/think if there is way to do this without a join....

qvqfqlik
Creator
Creator
Author

Even apply map is taking long time.

sunny_talwar

I don't know how you are using ApplyMap in this situation... can you show me the applymap based solution you have tried?